[RESOLVED] VBA Excel 2007: Dynamic Arrays

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!!!!!!! :wave:

Code:

---

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

---

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

vb Code:

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