Yes. A VBA For loop can count downward by using a negative Step, such as For i = 10 To 1 Step -1. The loop runs from 10 down to 1; if you omit Step, VBA uses a positive step of 1 instead.
Basic syntax for a negative step
A VBA For...Next loop has a counter, a starting value, an ending value, and an optional step size. To count down, make the step negative and set the start above the end. Declare the counter separately in VBA, then use it in the loop:
Sub CountDown()
Dim i As Long
For i = 10 To 1 Step -1
Debug.Print i
Next i
End Sub
This prints the integers 10, 9, 8, and so on through 1 in the Immediate window. The loop body runs once for each of those values. In the Visual Basic Editor, open the Immediate window with Ctrl+G on Windows; on Mac, the shortcut and editor layout can differ by keyboard and Excel version, so use the View menu if needed.
The step is the amount added to the counter after each pass. A negative step subtracts: Step -1 moves back by one, while Step -2 moves back by two. For instance, starting at 10 and ending at 1 with Step -2 visits 10, 8, 6, 4, and 2. It does not adjust the final value to force an exact landing on 1.
How VBA decides whether the loop runs
The direction of the step must agree with the starting and ending values. With a positive step, VBA can proceed toward an end value that is at least as large as the start. With a negative step, it can proceed toward an end value that is no larger than the start. The first condition is checked before the loop body runs. If the counter already lies beyond the end in the direction of travel, the body runs zero times.
For i = 5 To 1 Step -1runs five times, with values 5 through 1.For i = 1 To 5 Step -1runs zero times: the counter starts below the end, and a negative step moves it farther away.For i = 1 To 5uses the default step of 1 and runs five times.
VBA evaluates the start, end, and step expressions when it enters the loop. The counter then advances by the step at Next, and VBA checks whether it should run another pass. This matters if the end value comes from a variable that changes inside the loop: changing that variable does not reset the loop’s original endpoint. For clarity, calculate important limits before the loop and avoid changing the counter yourself unless you have a specific reason.
Useful countdown patterns
A negative step is not limited to counting down by one. It can traverse a range at a chosen interval, as long as the values and counter type make sense for the task. For example, this loop prints even numbers in descending order:
Sub PrintEvenNumbersBackward()
Dim n As Long
For n = 20 To 2 Step -2
Debug.Print n
Next n
End Sub
It prints 20, 18, 16, continuing through 2. If you need to include a particular endpoint, choose a start, end, and step that actually reach it. A loop from 20 to 1 by -2 does not visit 1, because each value remains even.
For an inclusive countdown from a variable number, put that number in the starting expression and use a suitable lower bound. For example, with lastItem holding the last position in a list, For i = lastItem To 1 Step -1 visits each position in reverse order. If lastItem is zero or negative, the loop does not run, which may be exactly what you want—or a sign that you should validate the input first.
Why reverse loops help when deleting worksheet rows
A common Excel reason to count downward is deleting rows while looping. If you scan from the top and delete a row, the rows below it shift upward. The counter then advances, which can skip the row that moved into the deleted row’s position. Starting at the bottom avoids that problem: deleting a row does not change the row numbers of the records still waiting above it.
This example removes rows where column A is blank, from row 2 down to the last used cell in column A. It assumes the worksheet is named Data and that column A contains ordinary values rather than error values:
Sub DeleteRowsWithBlankA()
Dim ws As Worksheet
Dim lastRow As Long
Dim r As Long
Set ws = ThisWorkbook.Worksheets("Data")
lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row
For r = lastRow To 2 Step -1
If ws.Cells(r, "A").Value = "" Then
ws.Rows(r).Delete
End If
Next r
End Sub
Starting at row 2 preserves the header in row 1. If your data has no header, adjust the lower bound. Also note that finding the last used row in column A only works for the intended task if that column is a reliable way to identify the data’s extent. If other columns contain records below the last entry in A, determine the last row using a more appropriate column or range.
Save a copy of the workbook before running a macro that deletes or changes data. VBA actions generally cannot be reversed with Ctrl+Z, and deleting rows may affect formulas, formatting, or references. Check the worksheet name and criteria carefully before running the procedure.
Choosing the counter type and step size
For most worksheet row numbers, use a Long counter. VBA’s Integer type has a much smaller range than Long, so it is unsuitable for many row-based loops. A Long can represent the row numbers used by modern Excel worksheets and is the usual practical choice for counting rows.
Use an integer counter for whole-number positions, such as row indexes or item numbers. Although VBA permits numeric step values beyond -1, floating-point counters can accumulate rounding differences. A loop intended to visit decimal values such as 0.3, 0.2, 0.1 may not behave as neatly as expected with a floating-point type. If the task is based on a fixed number of decimal places, consider counting whole units instead—for example, count 3, 2, 1 and divide each value by 10 when using it.
Also ensure that the counter can represent the value reached after the final pass. VBA advances the counter at Next before it finishes deciding that the loop is complete. If that advance causes an overflow for the chosen data type, the loop can raise an error even though the last value inside the body looked valid. A sufficiently wide type such as Long helps prevent this for ordinary worksheet indexes.
Common mistakes and how to fix them
- Using a positive step for a countdown:
For i = 10 To 1does not count down. The omitted step means +1, so the loop does not run. AddStep -1. - Reversing the start and end accidentally:
For i = 1 To 10 Step -1has a negative step but starts below the endpoint, so it runs zero times. Start at the larger value when counting downward. - Expecting the loop to hit an unreachable endpoint: A step of -2 from 10 visits even numbers, not 1. Choose a step that reaches the needed value or handle the endpoint separately.
- Using a zero step: A step of zero cannot move the counter toward completion and is not a valid way to create a repeating loop. Use a nonzero step and a clear exit condition.
- Changing the counter inside the body: Manually subtracting from the counter as well as using a negative step can make the loop skip values or finish unexpectedly. Let
Nextcontrol the counter in ordinary loops. - Assuming a loop will always run once: VBA checks the initial condition first. Validate user-provided start and end values if zero passes would be a problem.
When a loop behaves unexpectedly, temporarily add Debug.Print i inside the loop or step through the code with F8 in the Visual Basic Editor on Windows. This makes the sequence visible and helps distinguish a direction error from a wrong endpoint or step size.
For…Next compared with other loop choices
Use For...Next when the number of steps or the counter range is known and changes predictably. A negative step is especially natural for reversing a numbered range. The loop’s direction is visible in the code, which makes it easier to review than manually reducing a counter in a different loop structure.
Use For Each...Next when you want to process every object or item in a collection, such as each worksheet in a workbook or each cell in a range. It does not normally give you an index to decrement, so it is not a direct replacement when you specifically need to visit numeric positions backward. For a condition-driven task where the number of passes is not known in advance, a Do While or Do Until loop may be a better fit. Choose based on whether the task is defined by a count, a collection, or a condition.
Running and checking VBA in Excel
In desktop Excel for Microsoft 365 on Windows, open the Visual Basic Editor with Alt+F11. On a Mac, Option+F11 is a common equivalent, though some keyboards require Fn as well; Excel for the web does not run VBA macros. In the editor, a procedure can be placed in a standard module and run from the editor or from Excel after the workbook is saved in a macro-enabled format such as .xlsm.
Macros may be blocked or require approval depending on your organization’s security settings. Only run code from a source you trust, and do not change Excel’s macro security settings simply to make an unknown macro run. For a harmless countdown, use Debug.Print to inspect values before connecting the loop to actions such as deleting rows or overwriting cells.
Frequently Asked Questions
Can a VBA For loop count backward?
Yes. Give it a negative step, such as For i = 5 To 1 Step -1. It counts 5, 4, 3, 2, and 1.
What does Step -1 mean in VBA?
It means the counter is reduced by 1 after each pass through the loop. A different negative number changes the size of each decrement.
Why does For i = 1 To 10 Step -1 not run?
The counter starts below the endpoint, while the negative step moves it lower still. To count down from 10, start at 10 and set the endpoint to 1.
Can I use Step -2 or another negative number?
Yes. The loop subtracts that amount each time. Check that the sequence reaches the values you need; a step of -2 will not visit every integer.
Why use a negative step when deleting Excel rows?
Deleting from the bottom upward avoids shifting unprocessed rows into positions the loop has already passed. It is a common way to prevent skipped rows in a deletion loop.
Related
- Data Validation and Reconciliation in Excel Explained
- PowerPoint Table Diagonal Split Cell: Practical Methods
- Data Validation and Quality Assurance in Excel
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- VBA Copy Paste Mode: How to Clear and Control ItIn Excel VBA, copy/paste mode is Excel’s temporary state after a cell or range has been copied or…
- VBA Move UserForm Without a Mouse: Keyboard MethodsYes. In Excel VBA, move a UserForm in code by changing its Left and Top properties; to move…
- Excel VBA: Copy a Column and Paste Values Only SafelyTo copy a column in Excel VBA and paste only its values, copy the source column and use…
- VBA Access Arrays: Declare, Loop, and Resize ThemIn Microsoft Access VBA, an array is a variable that holds multiple values of the same declared type,…
- Conditional Formatting VBA: Rules, Examples, and TipsIn Excel for Microsoft 365 on Windows, VBA creates conditional formatting by adding a rule to a range’s…
- Excel VBA If Statements: Syntax, Examples, and TipsIn Excel VBA, an If statement runs code only when a condition is true; use ElseIf for additional…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.