Excel VBA: Running a Backwards For Loop

Get FREE Advanced Excel Exercises with Solutions!

In this article, we will discuss how to use the For Loop statement in Excel VBA to loop through a range of cells backwards. We will explain the syntax of the For Loop statement and provide several examples of how to use it to delete rows, count down, as well as assigning values in cells.


Download Practice Workbook

You may download the following workbook to practice yourself.


4 Examples of Running a Backwards For Loop in Excel VBA

In this section, we will demonstrate five practical examples of using Nested For loops in Excel with appropriate illustrations.


1. Countdown Using Backwards For Loop

 

Often we need to perform some sort of countdown in some moments. VBA helps us in this aspect by providing a looping feature. In the following example, we will use For loop in the reverse direction to countdown from 10 to 1. The result will look like the above illustration.

To do that, we will use the following code.

Sub For_Reverse_Counter()
Dim i As Integer
'Using For Loop to Count Backwards
For i = 10 To 1 Step -1
MsgBox i
Next i
MsgBox "Countdown Off"
End Sub

When we write the code in the VBA editor, the editor looks like the image below.

Code for counting down with backward VBA loop

🔎 How Does the Code Work?

For i = 10 To 1 Step -1

This iterates from 10 to 1 with a step of -1, which implies it is counting in the backward direction.

MsgBox i

This is the code to show the desired value in the MsgBox. In this case, the output is the present value of i.

Executing the Code

Now let us run the code by pressing F5 or by opening the Macro window from the Developer tab and we will see the output as follows.

Excel VBA For loop Backward Counter Result_01

After this MsgBox, a new one appears containing the number 9.

Excel VBA For loop Backward Counter Result_02

It will continue to show the MsgBox until the number reaches 1. After that the final MsgBox appears and the code finishes.

Excel VBA For loop Backward Counter Final Result


2. Assigning Values to Cells Using Reverse For Loop

Overview of Assigning Values Using Reverse For Loop

In order to assign value to some of our worksheets, we may use a For loop. Also, it is possible to use For loop backward to do the same thing as well. In the following code, we will assign some fixed value in the second column of our desired range using For loop backward.

Sub For_loop_backwards()
Dim i As Integer
'Giving the desired cells a value that we need
For i = 10 To 1 Step -1
'The value we want to assign is 10 times the value of i
ActiveSheet.Cells(i + 4, 2).Value = i * 10
Next i
End Sub

When we write the code in the VBA editor, the editor looks like the image below.

Assigning Values by For Loop Backwards

🔎 How Does the Code Work?

For i = 10 To 1 Step -1
ActiveSheet.Cells(i + 4, 2).Value = i * 10
Next i

The first line in the code is a backward For Loop which iterates from 10 to 1 with a step of -1.The line of the code works for the second column of row 5 to row 14 in the reverse direction and assigns the value 10 times to the value of i.

Execution and Viewing Output

In order to run the code we press F5 or by open the Macro window from the Developer tab and we will see the output as follows.

Output of Code For Loop Backwards


3. Hiding Specific Rows Using For Loop Backwards in Excel

Overview of Hiding Rows in a Sheet

We may use the For loop backwards to hide a row based on some criteria. For illustration, in the following dataset, we hide some rows when the age of a person is greater than 40.

Hide Rows in a Sheet Dataset

To hide the rows, we will run the code below.

Sub Filter_for()
For i = 10 To 5 Step -1
If Cells(i, 3) > 40 Then
Cells(i, 3).EntireRow.Hidden = True
End If
Next i
End Sub

When we write the code in the VBA editor, the editor looks like the image below.

Code to Hide Rows Using For Loop Backwards

🔎 How Does the Code Work?

For i = 10 To 5 Step -1
If Cells(i, 3) > 40 Then
Cells(i, 3).EntireRow.Hidden = True

The If function checks here whether the value in a cell in the third column is greater than 40. If this is true then the next line hides that specific row of the worksheet using EntireRow.Hidden property. While the For Loop in the code iterates from 10 to 5 with decrement size of -1.

Execution of Code and Viewing Output

We will run the code by clicking F5 or by opening the Macro window from the Developer tab. As a result, we will see the following result.

Result of hiding some Rows in Excel VBA


4. For Loop Backwards to Reverse a String in Excel

Overview of Reverse a String by For Loop Backwards

Using For Loop Backwards in the following example ,we will reverse a string. The code to do this is like below.

Sub reverse_string()
Dim str1 As String
Dim str2 As String
Dim char As String
Dim leng As Integer
Dim i As Integer
For i = 13 To 5 Step -1
str2 = "" 'First declare str2 as empty string
str1 = Cells(i, 2) 'Assigning the value of cell to str1
leng = Len(Cells(i, 2)) 'Len function to find length
For j = leng To 1 Step -1
char = Mid(str1, j, 1) 'Mid function finds next character
str2 = str2 & char
If j = 1 Then
Cells(i, 3) = (str2)
End If
Next j
Next i
End Sub

When we write the code in the VBA editor, the editor looks like the image below.

Code to Reverse a String Using Excel VBA For Loop

🔎 How Does the Code Work?

str2 = str2 & char

This adds the new character found by For Loop to the existing string.

If j = 1 Then
Cells(i, 3) = (str2)

When the loop ends, it assigns the string to a cell.

Mid(str1, j, 1)

Mid Function returns the ‘j’th character from the string ‘str1’.

Len(Cells(i, 2))

Determines the length of  the targeted cells.

Execution and Output

We will run the code by clicking F5 or by opening the Macro window from the Developer tab. As a result, we will see the following result.

Result of Reverse String Code


How to Use Do While Loop for Counting Backwards in Excel VBA

It is possible to do the same work of looping backward by using the Do While loop. In the following code, we will run a loop that continues until n is greater or equal to zero in the backward direction.

Sub DoWhile_Reverse_Loop()
Dim n As Integer
n = 10
Do While n >= 0
MsgBox n
n = n - 1
Loop
End Sub

When we write the code in VBA Editor, it looks like the image below.

Do While Loop in Reverse Order

🔎 How Does the Code Work?

Do While n >= 0

This line continues the loop until the value of n is greater or equal to zero.

Why Do While Loop?

Usually we use the Do While loop when we don’t know the exact number of iterations. We give a condition in the code. Until the code finds that condition is fulfilled, it continues iteration. When the condition becomes false, the code stops working.


How to Exit a For Loop in Excel VBA

Overview of Exit a For Loop

It is necessary sometimes to exit the For loop on certain situation. In this example, we exit the for loop if the target cell is empty. We will use the Exit For command to do this.

Sub Exit_For_loop()
For Each Cell In Range("B5:B10")
If Cell.Value > 0 Then
Cell.Offset(0, 1) = "Positive"
ElseIf Cell.Value < 0 Then
Cell.Offset(0, 1) = "Negative"
ElseIf Cell.Value = "" Then
Exit For
Else
Cell.Offset(0, 1) = "Zero"
End If
Next Cell
End Sub

When you copy the code to VBA Editor,it will look the image below.

Exit a For Loop by Exit For Command

🔎 How Does the Code Work?

ElseIf Cell.Value = "" Then
Exit For

This part of code checks whether any cell in our desired range is empty. If it finds any empty cell it enters the loop and finds the command “Exit For” which breaks the loop and code looks for the next executable line in the next.

If Cell.Value > 0 Then
Cell.Offset(0, 1) = "Positive"

This code snippet checks whether the value in a cell is greater then zero. If true then it assigns the value “Positive” to the next cell of that specific row.

For Each Cell In Range("B5:B10")

This line of the code operates on all the cells in the range B5:B10.

Execution and Output

We will run the code by clicking F5 or by opening the Macro window from the Developer tab. As a result, we will see the following result.

For Loop Breaks Just After Finding a Blank Cell


Things to Remember

  • For loop in backward direction doesn’t work if we don’t define the exact step size i.e. the decrement in each step.
  • We should be careful while writing the syntax of the code since it is different from the forward loop.
  • It’s always a good idea to test backwards for loop with a small range of cells before applying it to a larger dataset.
  • When using a backwards for loop, it’s important to be careful with the loop counter variable. If we’re not careful, we may end up with an infinite loop.

Conclusion

That is the end of this article regarding Excel VBA For loop backwards. If you find this article helpful, please share this with your friends. Moreover, do let us know if you have any further queries. Finally, please visit Exceldemy for more exciting articles on Excel.

Junaed-Ar-Rahman
Junaed-Ar-Rahman

Hello! Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed BSc in Biomedical Engineering from Bangladesh University of Engineering and Technology. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo