CODEWITHSUNDEEP
arraysvbaloopsfor-loop
Dominika Zołotarew
Dominika Zołotarew

Reputation: 23

Looping through an Array backwards (Bottom to the top) VBA

I have created an array in which each element is a number of row I want to delete from the worksheet.

The only issue is that deleting rows from the top of the worksheet would shift position of other rows. Therefore, please advise how to loop through the array from the bottom to the top (backwards).

(...)
For Each r In rowArray()

    Cells(r, 5).Rows.EntireRow.Delete

Next r
(...)

Upvotes: 2

Views: 1844

Answers (2)

Mathieu Guindon
Mathieu Guindon

Reputation: 71177

For Each...Next loops vastly outperform For...Next loops for iterating object collections (source), and For...Next loops outperform For Each...Next loops for iterating an array. So, use a For loop if you're iterating an array, and a For Each loop it you're actually iterating the invidivual cells of a Range.

Consider using Union instead of iterating rows backwards, to join the rows you want to delete into a single Range object, which you an delete in a single worksheet operation - that will vastly outperform the backwards-loop.

Private Function Combine(ByVal source1 As Range, ByVal source2 As Range) As Range
    If source1 Is Nothing Then
        Set Combine = source2
    Else
        Set Combine = Union(source1, source2)
    End If
End Function

Something like this (untested air-code):

Dim toDelete As Range, i As Long
For i = LBound(rowArray) To UBound(rowArray)
    Set toDelete = Combine(toDelete, rowArray(i))
Next
If Not toDelete Is Nothing Then toDelete.EntireRow.Delete

That way toggling Application.Calculation to manual, disabling Application.EnableEvents and setting Application.ScreenUpdating to False will have little to no impact whatsoever on overall performance, because you're only interacting with the sheet when you have to, whereas deleting each row individually will fire worksheet events, prompt recalculations and screen repaints at every single iteration.

Upvotes: 5

Absinthe
Absinthe

Reputation: 3391

Use a counter loop and the step command:

Dim x as Integer

For x = 10 to 1 Step -1
   myWorksheet.Rows(x).Delete
Next x

If you have numbers in an array, it would be something like:

For x = ubound(myArray) to 0 step -1
   rows(myArray(x)).Delete
next x

Step is used to indicate how many iterations to jump, so you could use Step 2 for every other row, or Step -5 for every 5th row backwards.

Upvotes: 2

Related Questions