CODEWITHSUNDEEP
vbaexcelfor-loopforeach
John A
John A

Reputation: 33

How to Make a For-Each Loop Run Backwards

I have written a small script in VBA which checks the value of a cell in a given range against a list. If the cell values match a value in the list it is kept, else it is deleted. I was wondering how I could make it run backward, as running it forwards creates issues. I have researched this somewhat and I have tried appending 'Step -1' to the end of the line which begins the for loop, but this doesn't work in this case.

Set Rng = Range("A9:V9")
For Each cell In Rng
    If Not myList.Exists(cell.Value) Then
        cell.EntireColumn.Delete
    End If
Next

Upvotes: 3

Views: 272

Answers (2)

Vityata
Vityata

Reputation: 43585

In this case, probably some for-loop like this one would be enough:

Option Explicit

Sub TestMe()

    Dim rng As Range
    Dim cnt As Long

    Set rng = Range("A9:V9")

    For cnt = rng.Cells.Count To 1 Step -1
        Cells(rng.Row, cnt) = 23
        Stop
    Next

End Sub

I have put Stop so you can see which cell is referred. Once you hit the Stop, continue further with F5.

Upvotes: 6

Dave Reilly
Dave Reilly

Reputation: 65

Richard, you're entirely right that the "Step -1" approach would be the right solution in this case. You simply have to change the variable references around to work with the loop.

For example:

Set Rng = Range("A9:V9")
For i = rng.rows.count to 1 step -1
    for j = rng.columns.count to 1 step -1
        if not myList.Exists(rng.cells(i, j).value) then
           rng.cells(i, j).entirecolumn.delete ' This probably won't work, but you get the idea.
        end if
    next j
next i

Upvotes: 0

Related Questions