To make a VBA For loop count backwards, give it a negative Step, such as For i = 10 To 1 Step -1. The loop starts at 10, runs down to 1, and includes both endpoints; without Step -1, VBA counts upwards by 1.
How a VBA For loop works
A For...Next loop repeats a block of code while moving a counter through a sequence of values. Its general form is For counter = start To end Step increment. The counter is assigned the start value, the loop body runs, and then VBA adds the increment before checking whether the loop should continue. A negative increment makes the counter descend.
The Step clause is optional. When omitted, its value is 1, which is why a loop written as For i = 10 To 1 does not count down: the counter is moving in the wrong direction to reach the ending value. For a descending loop, use a negative step. The start and end values are inclusive when the step can reach them; for instance, counting from 10 to 2 by -2 visits 10, 8, 6, 4, and 2.
Declare the counter explicitly. For loops that use worksheet row numbers or other potentially large values, Long is usually a sensible type. It supports a much larger range than the older Integer type, which can overflow when used for large row numbers.
Basic countdown example
This example prints the numbers 10 through 1 in descending order. In the Visual Basic Editor, open the Immediate window to see the output, or change Debug.Print to perform another action for each number.
Sub CountDownExample()
Dim counter As Long
' Visit each whole number from 10 down to 1.
For counter = 10 To 1 Step -1
Debug.Print counter
Next counter
End Sub
The loop body runs ten times. After printing 1, VBA subtracts 1, the counter becomes 0, and the loop ends because it has passed the ending value in the direction of travel. If the values need to decrease by a different amount, change the step: Step -2 skips every other number.
Why loop backwards through worksheet rows
A descending loop is especially useful when you may delete rows as you go. Suppose you inspect row 5 and delete it. The row that used to be row 6 shifts up into row 5. If an upwards loop then advances to row 6, it skips the shifted row. A backwards loop starts at the bottom instead, so deleting a row does not change the position of the rows that the loop has not yet examined.
To process worksheet rows without deleting anything, the same pattern is useful when the task naturally starts at the end—for example, reading the newest entry first or filling a range from bottom to top. This example displays the values in column A, starting with the last used cell in that column and moving upward to row 2. It assumes row 1 contains headers.
Sub ReadColumnBackwards()
Dim ws As Worksheet
Dim lastRow As Long
Dim rowIndex As Long
' Set the worksheet and find the last used cell in column A.
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For rowIndex = lastRow To 2 Step -1
Debug.Print ws.Cells(rowIndex, "A").Value
Next rowIndex
End Sub
Change Data to the actual worksheet name. The End(xlUp) expression starts at the bottom of column A and moves upward to the first non-empty cell it encounters. If column A is blank, lastRow is 1 and the loop from 1 to 2 with a negative step runs zero times. If another column better identifies the last record, use that column instead.
Deleting matching rows safely
Here is a common practical use: delete rows where column A is blank, while keeping the header row. The macro begins at the last used row and works up to row 2. It tests the value in column A, and removes the whole worksheet row when that value is empty.
Save a copy of the workbook before running a macro that changes or deletes data. VBA actions generally cannot be reversed with Ctrl+Z, so a backup is the reliable way to recover the original rows.
Sub DeleteRowsWithBlankColumnA()
Dim ws As Worksheet
Dim lastRow As Long
Dim rowIndex As Long
' Use the worksheet that contains the records.
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
' Work upward so deleting a row does not skip the next record.
For rowIndex = lastRow To 2 Step -1
If Len(ws.Cells(rowIndex, "A").Value) = 0 Then
ws.Rows(rowIndex).Delete
End If
Next rowIndex
End Sub
This test treats a truly empty cell and a formula that returns an empty string as blank, because both produce a zero-length value here. If spaces should count as blank too, trim the cell text in the condition; be mindful that converting values to text may not suit every kind of data. Also confirm that column A is the right test: a row with an empty A cell but valid information elsewhere will be deleted by this example.
Arrays and collections in reverse order
Backwards loops are not limited to worksheet rows. For a VBA array with known numeric bounds, start at its upper bound and use its lower bound as the endpoint. The following example assumes a one-based array:
Sub PrintArrayBackwards()
Dim values(1 To 4) As String
Dim index As Long
' Add sample values, then print them in reverse order.
values(1) = "North"
values(2) = "East"
values(3) = "South"
values(4) = "West"
For index = UBound(values) To LBound(values) Step -1
Debug.Print values(index)
Next index
End Sub
UBound and LBound return the upper and lower bounds of an array, so this pattern avoids assuming that every array starts at 1 or has a fixed size. It works for an array with a numeric index range. For a VBA collection, use its Count as the first index and 1 as the last, because collections are indexed from 1. Check that the collection contains items before indexing it.
Use For Each when you simply need to visit every item and do not need a particular order or an index. It is often clearer for reading a collection. It is not a substitute for a reverse indexed loop when you need to delete items by position: changing a collection while enumerating it can disrupt the enumeration. Work backwards by index when removal order matters.
Common mistakes and how to fix them
- Leaving out the negative step:
For i = 10 To 1uses the default positive step, so it does not count down. AddStep -1. - Using the wrong sign: A loop from 1 to 10 with
Step -1starts below its endpoint while moving farther away. Use a positive step for an ascending range and a negative step for a descending one. - Starting beyond the endpoint in the wrong direction: A descending loop such as 1 to 10 with
Step -1runs zero times. Check that the start is at least as large as the endpoint when counting down. - Forgetting inclusive endpoints: A loop from 10 to 1 includes 1. If the final value should be excluded, set the endpoint or condition to match the intended range.
- Changing the counter inside the loop: Manually subtracting from the loop variable as well as using a negative step can make the sequence confusing or skip values. Normally let the
Forstatement manage its counter. - Using a small counter type: Worksheet row indexes can exceed the range of an
Integer. Declare row counters asLong.
When a loop appears to do nothing, inspect the start value, endpoint, and step sign together. When it runs too many times or skips records, check whether the loop body changes the range being traversed and whether the boundary is correct.
Choosing between For, For Each, and Do loops
Choose a backwards For loop when the order matters, when you need the counter value, or when deleting worksheet rows from a changing range. A For Each loop is often the cleanest choice for visiting every item in a fixed collection without needing an index. A Do While or Do Until loop is useful when the stopping condition is based on a changing value rather than a known number of iterations.
For example, if you need to check rows 2 through a known last row, a descending For loop makes the bounds visible and predictable. If you need to keep reading cells until a particular marker appears, a condition-based loop may express the task more naturally. The choice is about the structure of the job; there is no special backwards-loop command in VBA.
Running and adapting the macro
In Excel for Microsoft 365 on Windows, open the Visual Basic Editor from the Developer tab by choosing Visual Basic, insert a standard module, and paste the procedure there. If the Developer tab is not shown, it can be enabled in Excel Options under Customize Ribbon. Run the macro from the editor or Excel’s macro controls after reviewing the worksheet name, column, and row limits used in the code.
VBA loop syntax is the same in Excel for Mac, though the route to the editor and available security controls can differ from Windows. The examples use standard worksheet and VBA operations. A macro-enabled workbook must be saved in a format that supports macros, such as .xlsm, if you want to keep its VBA code. Do not run a macro from an untrusted source, and make a backup before any procedure that changes workbook data.
Frequently Asked Questions
How do I make a For loop go backwards in VBA?
Use a negative step, such as For i = 10 To 1 Step -1. The step controls how the counter changes after each pass.
Can a VBA For loop use Step -2?
Yes. For example, For i = 10 To 2 Step -2 visits 10, 8, 6, 4, and 2. Choose the endpoint to fit the sequence you want.
Why does my loop from 10 to 1 run zero times?
If you omit Step -1, VBA uses a positive step. The counter moves up from 10, away from 1, so the loop does not run. Add the negative step.
Should I loop backwards when deleting Excel rows?
Usually, yes, when deleting rows by index in a loop. Deleting a row shifts later rows upward, but a backwards loop has already passed those higher row numbers and continues safely through the remaining rows.
Does Step -1 include the ending value?
Yes, if the sequence reaches that value. A loop from 5 to 1 by -1 visits 1 as its final value. A step that does not land exactly on the endpoint stops after passing it.
Can I count backwards through an array?
Yes. Start at UBound(array), end at LBound(array), and use Step -1. This handles arrays whose lower bound is not 1.
Related
- Excel Data Validation Restrictions and Their Limits
- Data Validation Process: Steps, Checks, and Best Practices
- Data Validation Named Range in Excel: Setup and Fixes
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Copy Range to Another Sheet: Methods and TipsTo copy a range to another worksheet with Excel VBA, qualify the source and destination sheets and use…
- Excel VBA Get Username: Windows or Excel User Name?In Excel VBA on Windows, use Environ$("USERNAME") to read the current Windows account name. Use Application.UserName instead if…
- Excel VBA UserForm ListBox: Sorting Options and CodeTo sort a list in an Excel VBA UserForm, sort the values before adding them to the ListBox;…
- Replace Excel Macros? Alternatives and When to SwitchYes—many Excel macros can be replaced, but the right substitute depends on the task: use formulas for calculations,…
- Access VBA Find in String: InStr, Options, and ExamplesIn Access VBA, use InStr to find one string inside another: it returns the 1-based position of the…
- Refresh Worksheet VBA: Methods, Examples, and FixesTo recalculate formulas on one worksheet with VBA, call that worksheet’s Calculate method. To refresh imported data, use…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.