How to Create For Loops in VBA

You can add lots of automation to your spreadsheet by utilizing visual basic (VBA) and running macros. One of the more common macros you might run include loops. In particular, using for loops in VBA can allow you to cycle through a range of data, check for criteria, and then execute commands. Or, you can also use it to do calculations and to compute totals. There’s a lot of potential with running loops in VBA. In this post, I’ll show you how you can get started with them.

Looping through data in Excel to highlight blank or incomplete data

In the following data set, I have some missing values where there is an (*) in place of a value in column D:

I’m going to loop through the column that shows the change value and I will look for the (*) values.

To get started, launch VBA from the developer tab or hit ALT+F11. Then, insert a new module. I’m going to call this subprocedure ‘cleanup’. I’ll start with declaring variables for the worksheet, individual cell I’m looping through, an integer to track how far along I am in the range, and an integer for the last cell within the range.

Sub cleanup()

Dim ws As Worksheet
Dim cl As Range
Dim lastcell As Integer
Dim i As Integer

Set ws = ActiveSheet
lastcell = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row




End Sub

In the above code, I’ve assigned the worksheet variable to the sheet that I’m on. And I set the lastcell variable to the last row in the sheet. This is the same as if you were press F5->Special->Last Cell. This makes it easy to determine where the data set ends, and gives me an endpoint for my loop. There are other types of loops you can use where you don’t need to specify an end. But this reduces the risk of you getting into a never-ending loop.

As for the loop itself, it will look like this:

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Interior.Color = vbRed
            
        
        End If
        

Next i

How it works is it uses the i variable to start from 1 and go all the way until the lastcell variable. In this example, that relates to 92 (the last row in my data set).

Then, it assigns the cl variable to each cell in column D as I go through that range. In the first instance, the cl variable is D1, then D2, and so on, until you reach the end (D92).

Next, it evaluates if the value of that cell is (*). If it is, then it highlights the entire row in the color red. If I run this macro, this is what my sheet looks like afterwards:

It did the job well, as you’ll notice everything that had a value of (*) in column D, the entire row ended up getting highlighted in red. There are other colors you can use and other things you could have done. Next, I’ll show you how you can just delete these rows entirely.

Deleting rows while looping through data in Excel

Removing rows seems simple enough, but it is a little tricky here. For instance, to remove the row rather than to just highlight it, that involves a simple line of code (in my example I’m referring to my cl variable):

cl.EntireRow.Delete

If I run the macro with that code, this is what happens to my worksheet:

I’ve left the red highlighting in place to show you that there are still many rows that should have been deleted and that weren’t. So did the macro simply not work?

The problem lies with how it was set up. In the current macro, I’m moving down from one row to the next. If I’m at row 10 and delete it, then the next row I move on to is row 11. However, the issue is that once I delete row 10, everything moves up a spot. So what was previously row 11 now becomes row 10, causing me to effectively skip over that row and miss it. Now my macro no longer evaluates it.

There are a couple of ways to fix this. One can be that if I delete the row, I can adjust my i variable so that it deducts one so as not to skip over the next row. Just by adding a line of code, you can adjust for that issue:

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            i = i - 1
            
        
        End If
        

Next i

The i = i -1 line of code will reset the i variable back down a spot when a row is deleted. This will now prevent the macro from jumping over a row.

However, there’s another option you can use, and that’s looping through the data in the opposite direction.

Looping through the data backwards and utilizing the step keyword

In the first example, I looped through the data from row 1 to the last row. Here’s my full code for that:

Sub cleanup()

Dim ws As Worksheet
Dim cl As Range
Dim lastcell As Integer
Dim i As Integer

Set ws = ActiveSheet
lastcell = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            
        
        End If
        

Next i



End Sub

I’m going to adjust this and go in reverse order. However, it’s not as simple as specifying start from the bottom number and go to row 1. You need to give VBA a bit more information. This is where you can use the Step keyword. By using that, you can specify in which direction you want the loop to go, and whether it should go 1 row at a time or jump multiple rows.

Here’s how the loop looks like if I want VBA to jump backwards one row at a time from the bottom:

For i = lastcell To 1 Step -1


    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            

        End If
        

Next i

If you want to jump by 5 rows, you would use Step -5. However, because I want to evaluate each row, -1 is what I’ll use in this example. By running this loop, now the highlighted rows are all deleted:

Since the loop is starting from the bottom and working its way up, it doesn’t matter that I’m deleting rows; it doesn’t impact the rows above and so no adjustment to the i variable is necessary.

If you like this post on How to Create For Loops in VBA, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.