How to apply Step -1 in a For Next to loop in reverse order?

Question

I'm trying to get this loop to check xCol in reverse order, or from the right column to the left. It works without the Step -1 (going from left to right), but does nothing when I add the Step -1.

Sub Email()
    Dim xSheet As Worksheet
    Dim xRow As Integer
    Dim xCol As Integer
    Dim lastRow As Integer
    Dim lastCol As Integer

    Set xSheet = Excel.Worksheets("Approval Process")

    lastRow = xSheet.UsedRange.Rows.Count
    lastCol = xSheet.UsedRange.Columns.Count

    For xRow = 2 To lastRow
        For xCol = 3 To lastCol Step -1
            If xSheet.Cells(xRow, xCol).Value <> "" Then
                MsgBox xRow & "," & xCol
               Exit For
            Else
                MsgBox "Blank"
            End If
        Next xCol
    Next xRow

End Sub

Cells (C2,H5) have either the word "Yes" or are blank.

The end goal is to have the loop return the row and column number for the last cell with data in each row.

Make sure you change `Integer` into `Long` Excel has more rows than `Integer` can handle! Since there is no benefit in using `Integer` I recommend [always to use `Long` instead](https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long/26409520#26409520) — Pᴇʜ, Oct 23 '20 at 06:55

Salamander Krajza · Accepted Answer · 2020-10-23T06:52:14.577

1

When you have negative STEP in FOR loop you should start with bigger number and make the second argument smaller.

When you put smaller number as first input and bigger as second input there is a problem.

Try reverse values to:

For xCol = lastCol To 3 Step -1

edited Oct 23 '20 at 06:52

answered Oct 23 '20 at 06:32

Salamander Krajza

236
2
7

How to apply Step -1 in a For Next to loop in reverse order?

1 Answers1