How To Use VBA Loops In Excel (For Next, For Each, Nested)

Are you an Excel Power User who’s looking to improve your skills and accelerate your career?

Maybe you’ve already had a play around with Visual Basic for Applications (VBA) and have gotten pretty handy at using macros to solve repetitive tasks, but are now looking at ways to take it to the next level?

Well, one way that represents perhaps the single most significant milestone on the road to “real” programming is learning to write code that can do those useful things over and over again, as many times as you want.

Honestly, whenever I speak to expert programmers, one of the main reasons that they got into coding, was they were trying to automate a manual process in their own lives so that they didn’t have to keep doing it themselves 😉

This is where the power of computer code - versus performing actions manually - becomes truly evident, and one of the first tools we can use to unlock these automations is something called a loop.

The power of repetition

So what, exactly, are loops?

Loops are basically just a way of repeating actions with computer code, but at computer speed and computer scale.

For example

Imagine having to perform some similar operation to a million spreadsheet cells or 100 workbooks. It would take you practically forever, but Excel VBA can accomplish tasks like these in seconds.

This is where the power of programming really starts to reveal itself.

There are several varieties of loops in VBA, but we’re going to focus on the ones most useful for manipulating objects within the Excel ecosystem – especially ranges and collections of one or more spreadsheet cells.

In today's article, we’re going to cover:

• For Next Loops
• Nested Loops, and
• For Each Loops

So let’s break down how each of these works.

For Next Loops

For Next Loops are a programing structure in VBA that lets us repeat some action or actions defined by a chunk of code, a fixed number of times.

That number of iterations is usually defined in a “counter” variable, which is incremented for each cycle of the loop, until all iterations are complete.

For example

To help you get a feel for the syntax here’s a very basic example macro that will simply message box the first five integers to the user, right there in Excel.

Sub ForNext ()
Dim Counter As Integer
For Counter = 	1 To 5
MsgBox (Counter)
Next Counter
End Sub

So here’s how it works:

Within the subroutine, we first need to declare a counter variable (it doesn’t have to be named “Counter” by the way, but often is by convention), which is what's going to keep track of how many iterations the loop has executed. This counter variable will almost always be an integer, as is the case here.

Dim Counter As Integer

The For Next loop itself starts with the keyword For, after which we need to actually set the value of the counter variable we just declared. This is so our loop will have some kind of starting point.

The number 1 is a common choice for many purposes (including this one, obviously), but you can technically make this any integer you want.

For Counter = 1

After setting Counter equal to 1, we now need to tell the loop how many iterations we want it to cycle through, starting with the value we set the counter variable to. In this case, that's 5, so the complete first line of the loop ends up being:

For Counter = 1 To 5

This means the loop will run for five cycles, with the counter variable being incremented from 1 to 5, in increments of 1.

You can then close out the body of the loop with the Next keyword, followed by the name of the counter variable you're incrementing throughout each cycle of the loop:

Next Counter

With the structure of the loop in place, the last piece is to fill in the body of the loop with whatever actions we want to be repeated with each cycle of the loop.

And that brings us to perhaps the biggest key to the entire operation: the fact that we have access to our counter variable inside the loop, so we can use it in whatever operation we perform even as its value changes.

In our current example, the operation is super simple. We just want to message box that value so that the result appears in Excel.

To achieve this, the body of our loop ends up being as simple as message-boxing the counter variable:

MsgBox (Counter)

For example

If we run our macro like so...

We start by seeing the number 1 printed in the message box.

Followed by 2, then 3, 4, and 5, so our program is doing exactly what we designed it to do.

Huzzah!

Iterating over ranges of cells

In the real world, a hard-coded iteration count like the one from the previous example, in which we're telling our loop to iterate exactly five times or ten times is usually too inflexible to be practical.

A better solution is typically to allow either the user, or some logic within our program, to define the number of iterations that the loop will execute, which is what we’re going to do now.

For example

Let’s walk through a more practical example.

Here we have a macro that iterates over a one-dimensional range of cells (a single column of selected values), and then deletes any negative numbers that it finds within the range.

Sub RemoveNegatives()
Dim Counter As Integer
Dim RCount As Integer
RCount = Selection.Rows.Count
For Counter = 1 To 	RCount
If Selection.Cells(Counter, 1).Value < 0 Then
Selection.Cells(Counter, 1).ClearContents
End If
Next Counter
End Sub

And here’s the dataset we’ll be using as our example:

Our goal is for the macro to remove those negative numbers from this column.

So let’s break down what this code means:

A lot of this is quite similar to our first example. We still have a counter variable, and we’re still incrementing that counter variable up to some other value with a For Next Loop.

But unlike our first example, we're going to be looping through a one-dimensional column of values, as opposed to simply printing messages to the screen.

Simple enough right?

Now, in order to know how many times we need to loop, we need a way to capture the number of rows in the selected range. For the sake of simplicity, our program is assuming that a user has already selected their range of interest prior to running the macro.

So, assuming we already have our desired range selected, how can we determine the number of rows in that range, so that we'll know how many times our For Next loop needs to iterate?

Well, it turns out that the Selection object in Excel has a Rows property (and incidentally, a Columns property as well). And in turn, those Rows and Columns properties have a Count property. So we can use the following line of code to get the number of rows in the current selection:

RCount = Selection.Rows.Count

Note that we need to declare the RCount variable first. Then, we can set up the first line of our For Next loop to iterate from 1, all the way to the number of rows in the selection of cells:

For Counter = 1 To RCount

Next, inside the body of the loop, we need a way to dynamically target each of the cells in the selected range individually - a different cell for each cycle of the loop.

A great way of doing this inside a For Next loop is the Cells property, which allows us to target individual cells with just a row number and a column number.

Now while the Cells property operates on the Excel grid as a whole by default, it can also operate within a selected range, as though that range is the entire grid.

For example

If I select a range and then target Cells (1,1) within that range, it will just return the top-left cell of the range regardless of what my selection is.

We can leverage this in our example, by targeting the different cells in the selected range using the changing value of the counter variable inside the loop.

Now going back to the motivation for our program, we really only want to take action if the value of one of the cells is a negative number.

So need to also set up an If statement inside the loop to test for that condition. But to apply the logical test, we need to access the individual cells, which we can do with the Cells property:

If Selection.Cells(Counter, 1).Value < 0 Then

For the row argument of Cells, we need to use the counter variable, which increments for each cycle of the loop – meaning the code will select a different row for every cycle of the loop.

But for the second argument of Cells, we can just use the number 1, since our selection only has 1 column, and we obviously want to select the 1st column of those 1 columns!

Then, in the body of the If statement, we just need to clear the contents of that particular cell, which will effectively delete the negative number. So we can borrow the code we used in the logical test of the If statement to target the cell, and then simply chain VBA's ClearContents method to the result:

Selection.Cells(Counter, 1).ClearContents

And that’s all we need to clear up the contents of the targeted cell for a particular cycle of the loop IF the value of that cell is negative.

So now, just to confirm and see that this works, I’ll highlight this array of numbers (note that four of them are negative)…

And then if we run our macro like so:

We see that the four negative numbers are missing, so our code worked!

And the cool thing about VBA programs like this which incorporate loops, is that they can scale to an arbitrary level. We're not limited to iterating over ten rows, or a hundred rows, or even a thousand!

And sure, at some point you’re going to bump up against the processing power of your computer if you're trying to apply a loop over millions of cells of data, or if your machine is 10 years old. But for most practical purposes, loops are going to be able to handle whatever you throw at them.

This means that you could just as easily use this program to delete the negative numbers from a column of numbers with thousands of values. Pretty neat I’d say!

OK so let’s look at the next loop we can use.

Nested Loops

So far we’ve run our loop through a single column of data, but what if we want to perform the same kind of operation on two-dimensional data like we see below?

It won't be enough to simply loop through every cell in one column. Instead, we'll have to loop through every row of the data, and then for every iteration of that loop, execute another loop that cycles through all the cells in that row.

That's right - this means one loop inside of another loop, with the inner loop going through its full cycle for every iteration of the outer loop.

These Nested loops are useful in a wide range of applications but none more common or important than performing operations over a two-dimensional range of cells; that’s to say, a range consisting of multiple rows and multiple columns of data, like so:

Now this may sound a little hairy, and it can be a bit much to wrap your head around, but it's actually a fairly natural evolution of the For Next loop we've already set up.

To see how Nested loops work, let’s improve on our existing code to iterate through every cell in a two-dimensional range, again deleting any negative numbers it finds.

Here’s that original code, just to refresh your memory:

Sub RemoveNegatives() 	
Dim Counter As Integer
Dim RCount As Integer
RCount = Selection.Rows.Count
For Counter =1 To RCount
If Selection.Cells(Counter, 1).Value < 0 Then
Selection.Cells(Counter, 1).ClearContents 	
End If
Next Counter
End Sub

So here’s how this will work:

For our modified solution to work on a two-dimensional range, the current loop that we've already set up will still need to do what it's doing now and iterating through every row of the data from the first column.

But because we also have more columns of data to work on, we now need a 2nd loop inside that first one that moves it across to the next column until all rows and columns with data have been worked through.

To put it simply:

One loop working down the column and performing the task, the other loop moving it to the next column-almost like someone mowing a lawn.

Now since we're adding a second For Next loop, we're also going to need a second counter variable. When you're dealing with Nested loops like this, you will typically have two counter variables, which are often consecutive letters of the alphabet. Sometimes you'll see “i” and “j”, but I usually just go with “A” and “B”.

So for the first phase of our macro update, we’ll take the following steps:

• Change the name of the counter variable to “A”
• Change all references to that counter variable accordingly
• Add a second counter variable called “B”

Simple!

You can see these changes reflected in the code below:

Sub RemoveNegatives()	
Dim A 	As Integer
Dim B 	As Integer
RCount = Selection.Rows.Count
For A = 1 To RCount
If Selection.Cells(Counter, 1).Value < 0 Then
Selection.Cells(A, 1).ClearContents
End If
Next Counter 	
End Sub 

Next, just inside of our current For Next loop, we’ll start a second For Next loop as follows:

For B = 1 To

But now the question becomes: what do I iterate my second counter variable “B” up to?

We already know we need our A variable to iterate all the way up to the number of rows in our data, because the loop it's associated with is looping through all those rows.

But for our inner loop, we want to iterate up to the number of cells in a particular row… so how can we get to that number?

Well, the number of cells in a row of our selected range is the same as the number of columns in our selected range. And we can exploit this by setting up another variable that captures the count of columns in our selection.

So let's create another variable called CCount, similar to our existing RCount variable, and set its value to be the count of columns in our selection.

(CCount for columns, RCount for rows)

It will look like this:

Dim CCount As Integer
CCount = Selection.Columns.Count

Equipped with this value for our second counter variable to iterate up to, we can now finish the shell of our inner loop as seen below.

Note here that the body of our original For Next loop – the If statement, basically – is now inside the inner loop. As you’ll see, this is key to iterating over both the rows AND columns of the selected range:

Sub RemoveNegatives()
Dim A As Integer
Dim B As Integer
Dim RCount As Integer
Dim CCount As Integer
RCount = Selection.Rows.Count
CCount = Selection.Columns.Count	
For A = 1 To RCount
For B = 1 To CCount
If Selection.Cells(A, 1).Value < 0 Then
Selection.Cells(A, 1).ClearContents
End If
Next B
Next A 	
End Sub 

Now that we've laid the foundation for our solution, we can get down to the real value of Nested For Next loops.

So inside of the inner loop, we have access to both the A variable, which is keeping track of a given row in the selected range AND our B variable, which is keeping track of the given column.

Consequently, within this inner loop we can leverage our program’s awareness of what row we're on, and what column we're on within that row, to target a given cell in our two-dimensional range of cells.

In our current code, while we dynamically specify the row of a cell using a counter variable, we're still hard coding the column:

If Selection.Cells(A, 1).Value < 0 Then 
Selection.Cells(A, 1).ClearContents 
End If

But with a second counter variable at our disposal, we can dynamically specify both the row AND the column inside the inner loop. Doing that yields the final version of our Nested loop:

Sub RemoveNegatives()
Dim A As Integer
Dim B As Integer
Dim RCount As Integer
Dim CCount As Integer
RCount = Selection.Rows.Count
CCount = Selection.Columns.Count
For A = 1 To RCount
For B = 1 To CCount
If Selection.Cells(A, B).Value < 0 Then
Selection.Cells(A, B).ClearContents
End If 
Next B 
Next A 
End Sub 

With our macro fully transformed, I’ll make sure our target range of cells is selected:

Then run the macro…

(drumroll please) 🥁

Sure enough, it worked! Our little VBA program was able to parse through this two-dimensional range of rows and columns, identify all the negative numbers, and then delete them.

And just as with our 1 Dimensional For Next Loop, there’s absolutely no reason, besides the limits of your computer’s processing power, that this solution couldn’t scale to 2D ranges of much bigger dimensions!

So now, let’s look at the 3rd and final loop that we’re going to cover.

For Each Loops

Although For Next loops are perfectly capable of iterating through a two-dimensional array of cells, there's another kind of loop that is more ideally suited to this sort of task: the For Each loop.

So what do I mean by “this sort of task”, exactly?

In short, looping over a collection of objects in Excel - like the collection of all worksheets in a workbook, or (more pertinent to our recent example) the collection of all cells in a range of cells.

For Each loops are optimal for looping through members of a collection, without needing to worry about calculating the number of iterations needed and advance.

For these specific scenarios - iterating over collections of Excel objects - the For Each loop is often quite simply a faster, easier, and more intuitive alternative to the For Next loop.

So with that in mind, let's edit our last example macro, which deletes all the negative numbers in a selected two-dimensional range of cells, using a For Each loop.

Here’s how it works

First off, since we no longer need to specify the number of iterations that our loop will take in advance, we no longer need to capture the dimensions of the selected range (the number of rows and the number of columns).

We also won't need either of our counter variables to track the number of iterations inside the loop, so we can delete all of these lines of code from the macro, like so:

Sub RemoveNegatives()
For A = 1 To RCount
For B = 1 To CCount
If Selection.Cells(A, B).Value < 0 Then
Selection.Cells(A, B).ClearContents
End If
Next B
Next A
End Sub

However, we’ll need to create a new variable to hold the individual members of the collection that we're iterating through. That collection, of course, is the range of cells the user has selected.

Important:

The value of this variable will constantly change as the loop executes, to point to each individual member of the collection. Which in our case translates to “each individual cell of the selected range”.

We can declare this variable - which for clarity I’ll call “Cell”, since the objects we're iterating over are cells – with a Range data type since cells are just ranges in and of themselves:

Dim Cell As Range

With this variable in place, we can remove the For Next loops from our code, and begin setting up the shell of our For Each loop (note the singular “loop”: we only need one to iterate over a two-dimensional range, no Nested loops required!)

The first line of the loop looks like this:

For Each Cell In Selection

Let’s unpack this a bit.

• First, we have the keywords “For Each”
• Then we specify the variable that's going to hold each individual member of the collection that we're iterating over, which of course will be the Cell variable that we just declared
• And then the keyword In
• Finally, we specify the collection of objects that we're iterating over. In our case, it’s just the Selection object, which is the collection of whatever cells the user has currently selected

We can then close the loop, so to speak, in much the same way that you close out a For Next loop. We do this by using the keyword “Next”, followed by the name of the variable we’re tracking iterations of the loop with.

So here’s what our loop looks like so far:

For Each Cell In Selection
If Selection.Cells(A, B).Value < 0 Then
Selection.Cells(A, B).ClearContents
End If
Next Cell

So now that we've got the shell of our For Each loop put together, we can turn our attention to refactoring the code inside the loop.

But how?

Well, in each cycle of a For Each loop, we have access to exactly one object in the overall collection. That means for each cycle of our For Each loop, we have access to exactly one cell in the selected range. And this cell is captured in our Cell variable.

This means we can leverage our Cell variable in each iteration of the loop, to do things with the cell - such as access its value (Cell.Value) and clear its contents (Cell.ClearContents). This yields the following finished version of our code:

Sub RemoveNegatives()
Dim Cell As Range
For Each Cell In Selection
If Cell.Value < 0 Then
Cell.ClearContents
End If
Next Cell
End Sub

That’s a little more readable and straightforward than a Nested For Next loop, eh!?

Even at first glance, it’s pretty clear what the code is doing:

For each cell in the current selection, it tests whether the value of that cell is less than 0. If so, it clears the contents of the cell. If not, nothing happens.

Easy peasy!

So just to make sure this is working, I’ll highlight a copy of my 2D range of cells…

Then run the updated macro:

Huzzah!

It looks like it successfully did the same job as our Nested For Next loops, but with around half the lines of code.

And while it's true that For Each loops are typically more user-friendly than For Next loops (especially Nested For Next loops), it's important to note that they're limited to use cases in which you're iterating through a collection of Excel objects (like cells in a range).

However, For Next loops can be used in pretty much any situation where you need to iterate some specific number of times, making them more versatile.

For Next loops can be especially useful when we want to empower our end users to specify the number of times a certain process or operation should be repeated.

For Example

Let's say that we'd like to create a simple program that writes out a list of numbers from 1 to N, starting in the active cell, AND, it prompts the user to specify how long that list should be in.

In other words, the value of N.

Since For Each loops are limited to iterating over collections of objects, they can't help us here. However, this sort of problem is light work for the For Next loop:

After prompting the user to enter a number via the input box...

We simply use a For Next loop to iterate from 1 to that specified number, with each cycle of the loop writing the current value of the iterator variable i, to a spreadsheet cell that's offset one additional row from the active cell, like so:

The result is a program that can write out 1 to 10, 1 to 1000 and theoretically, all the way up to the million plus rows in an Excel worksheet. All that power and flexibility with just a few lines of code.

In short, there's a place for both of these loops in your Excel VBA toolbox!

Do you want to learn more?

With these 3 loops you’ll now be able to automate and simplify your tasks in Excel, but it doesn't end here. I cover loops, data visualization, macros, functions, formulas, and more in my Excel Bootcamp.

Go check it out now and see how much easier you can make your own day to day tasks!