Question regarding for loops vs arrays

[Huby00]

Hello,

Thanks to the members of this forum i have gone from completely clueless to beginning novice in vba. To improve, I am trying to get in the habit of creating code that is more efficient and faster.

I usually create for loops to run through the data in excel to delete unwanted values (such as an error in recording software that gives a 11111 value, for example).

Lastrow = Range("A65536").End(xlUp).Row
    For i = Lastrow To 1 Step -1
        If Cells(i, 5).Value = 11111 Then
        Rows(i).Select
        Selection.Delete Shift:=xlUp
        End If
    Next i

When i have a long data sheet of 20,000+ entries per column it takes a while.

My question is, would it be faster to create an array to store the row numbers of when 11111 and then delete them?


something like:

Dim counter As Integer
Dim deleterows() As Integer
counter = 0

Lastrow = Range("A65536").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, 5) = 11111 Then
    deleterows(counter) = i
    counter = counter + 1
End If
For i = counter To 0 Step -1
    Range(deleterows(counter) & ":" & deleterows(counter)).Delete Shift:=xlUp
    Next i
End Sub

Does anyone know how to delete ALL the rows at once without using a for loop? i tried on macro recorder but it gives me something like range("x:x, y:y, z:z.... etc

 

[jim may]

You could use an auto-filter filtered on 11111 in your column; then delete all.

 

[Huby00]

Thank you for that idea! worked great