[RESOLVED] VBA Excel 2007: Dynamic Arrays

[bshafter]

Hey everyone,

I am working on a code that will add a cell that contains the word "Total" to an array and skip everything else. I could conceivably just delete the rows of everything that doesn't contain "Total" and create an array that way but I am trying to avoid deleting data. This code works sometimes and others it does not. Any sight would be very much appreciated. Thanks!!!!!!!

Sub companyarray()

Dim myarray() As String
Dim index As Integer
Dim xrow As Integer
Dim size As Integer
xrow = 2
size = 1
index = 0 

ReDim myarray(size)


Sheets("Pivot Information").Select

Range("F3").Select

''''''FILL ARRAY
Do Until Cells(xrow, 6).Value = ""

If ActiveCell.Value Like "*Total*" Then
    Cells(xrow, 6).Select
    myarray(index) = Cells(xrow, 6).Value
    
    size = size + 1
    ReDim Preserve myarray(size)
    index = index + 1
    xrow = xrow + 1
Else:
    xrow = xrow + 1

ActiveCell.Offset(1).Select
End If
Loop

[westconn1]

avoid using select, selection, activate or active anything
try like

index = 0
redim myarray(index)
with Sheets("Pivot Information")
 
''''''FILL ARRAY
Do Until .Cells(xrow, 6).Value = ""
 
If .cells(xrow, 6).Value Like "*Total*" Then
    myarray(index) = .Cells(xrow, 6).Value
     index = index + 1
   ReDim Preserve myarray(index)
End If
xrow = xrow +1
Loop
end with