Excel VBA For Loops – A Beginners Guide

Friday, December 20, 2019 VBA Quick Training,

Excel VBA For Loops – A Beginners Guide

When learning Excel VBA, you need to know how to use loops competently.

By using loops we can walk through a range of values, rows, sheets or other objects and perform a task repeatedly on each one.

This is a fundamental Excel VBA skill. Not being able to efficiently construct loops will heavily limit the macros you can create.

There are 2 types of loop in VBA. There are For loops and Do loops. This article focuses on learning For loops.

If you want to learn Excel VBA fast. Enrol in our online course. This is a complete beginners guide to learning Excel VBA.

Introduction to For Loops in Excel VBA

There are 2 types of For loop in VBA – the For Next and the For Each Next. This tutorial will fully explain both.

The For Next loop is used when you need to perform a sequence of tasks a specified number of times.

You specify how many times you want the code to run, and what task to perform each time it runs.

The number of times the code runs is either known by us – perform these tasks 12 times (once for each month of the year), or by Excel – perform these tasks for each sheet in the workbook (Excel knows how many sheets are in the active workbook).

Let’s look at some examples of the For Next Loop.

Loop Through Each Row in a List

Looping through each row in a list is a very typical example of a loop. Reasons for doing this could be to analyze the value in column A of each row, and format or perform calculations on specific values.

In the code below, we loop through each row in a list until the total number of rows is reached. Each value in column A of the list is doubled.

This is a simple example of such a loop, but it demonstrates the structure of a For Next loop and its capability.

Sub DoubleValues()

Dim r As Long
Dim TotalRows As Long

TotalRows = Application.CountA(Columns(1))

For r = 2 To TotalRows
 Cells(r, 1).Value = Cells(r, 1).Value * 2
Next

End Sub

The For loop begins from row 2, and finishes after the row number is equal to the total number of rows.

Adding a Step to the For Next Loop

If you wanted to double every other row in the list, you could add a step.

In the code below, the loop steps 2 rows each time it loops, instead of each row.

Sub DoubleValues()

Dim r As Long
Dim TotalRows As Long

TotalRows = Application.CountA(Columns(1))

For r = 2 To TotalRows Step 2
 Cells(r, 1).Value = Cells(r, 1).Value * 2
Next

End Sub

The Step 2 statement is added at the end of the first line of the For Loop.

Loop Through Each Character in a Cell

Another example of a For Next loop is to walk through each character in a cell. It could be that you want to extract, format or remove part of a cells content.

In this example, we will format all the numbers within a cell red.

Sub FormatNumbers()

Dim i As Integer
Dim r As Long
Dim TotalRows As Long

TotalRows = Application.CountA(Columns(1))

For r = 2 To TotalRows

For i = 1 To Len(Cells(r, 1))
 
 If IsNumeric(Mid(Cells(r, 1), i, 1)) Then
 Cells(r, 1).Characters(Start:=i, Length:=1).Font.Color = 255
 End If
 
 Next i

Next r

End Sub

If you are a beginner to VBA this code may look a little daunting, but it is rarely as bad as it seems.

We have 2 For Next loops here. One to loop through the rows in a list, and a deeper one to loop through the characters of a cell.

A couple of text functions (Len and Mid) have been used to find the length of the string and also work on each character.

The colour code of 255 has been used to format the characters red.

See how to format whole words in a cell a specific colour with a macro.

Loop Through the Cells of a Selected Range

Now let’s look at the For Each Next Loop.

This loop is wonderful when working with collections. Examples of collections include all the sheets of a workbook, all the open workbooks or all the cells when a selected range.

The macro below converts all the values in the selected range to uppercase.

Sub ConvertToUpper()

Dim r As Range

For Each r In Selection
 r = StrConv(r, vbUpperCase)
Next

End Sub

Such as simple, yet useful little macro.

A variable is declared named r to act as a cell. This is used in the For Each Next loop to go to each cell in the selected range and convert to uppercase.

Loop Through the Sheets of a Workbook

Another awesome example of the For Each Next loop being used with a collection, would be with all the sheets of a workbook.

In this macro, we will freeze the top row of every sheet in a workbook.

Sub FreezePanesAll()

Dim s As Worksheet

For Each s In ActiveWorkbook.Worksheets
 s.Activate
 Range("A2").select
 ActiveWindow.FreezePanes = True
Next
End Sub

Excel knows how many sheets are in a workbook, so it is not necessary for us to. We just use a For Each Next loop through Activeworkbook.Worksheets.

Check out this example where we export all sheets as a PDF.