Doing loops in VBA is very useful because it allows you to go through a range of cells, evaluate each, and then take an action if a specified criteria is met. I am going to go through a couple of examples to show you how you could do this to highlight cells and also delete rows.
For a brief intro to macros check out my earlier post about how to access VBA and record macros.
I have a data set below that shows population by country in the year 2015:
First, what I will do is do a loop to evaluate each country’s population for 2015, and for any that had more than 1,000,000 people, highlight the country name in column A.
There are a number of ways to do loops in VBA but I prefer using the For…Each loop.
The key thing is first to define the range of cells I want the loop to be applied to. I can use Excel’s COUNTA function to count the range. To do so, I first setup a variable called totalcount:
totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
WorksheetFunction allows me to use Excel functions that are accessible in a normal spreadsheet and are accessible without VBA, in this instance, COUNTA. The totalcount variable now tells me how big my range needs to be; to what row I need my loop to go to.
I created a couple of additional variables as well: cl – this is the specific cell that will be evaluated when the loop is in progress; ws – this is just the worksheet which allows me to shortcut having to spell out the name of the worksheet each time (in this case it is just the default Sheet1). Below are my variable declarations:
Dim cl As Range
Dim totalcount As Integer
Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)
totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
The first line in my loop specifies the range that I am going to cycle through:
For Each cl In ws.Range(“A2:A” & totalcount)
The above can be interpreted as saying that I am going to cycle through each cell from the range A2 until A and whatever the totalcount is equal to. I have to close the loop as well, so the last line of the loop is as follows:
Next cl
The point of the above line is that once I finish evaluating the cell and making whatever changes I need I now move onto the next cell to evaluate. This variable does not have to be called cl, it just makes it easier to think of instead of next a or b let’s say.
So that is the loop. But without anything inbetween the start and end of the loop all the sub procedure is going to do is go through every cell in that range and do nothing. So as mentioned I want to determine if the population is more than 1,000,000:
If cl.Offset(0, 1) > 1000000 Then
I use the offset function to evaluate the column adjacent to colum A. I could have selected my range as being B rather than A and I wouldn’t need to do an offset. However, using offset(0,1) tells the procedure to look at the column to the right. Just like the regular excel function, the first argument in the offset function is the number of row offsets, and the second is the column offset. So the above line evaluates if the related value in column B is more than 1,000,000. The next line I enter is what will happen if it is true:
cl.Interior.Color = vbYellow
This will highlight the cell in yellow. There are a lot of things you can do and in VBA once you type the period after your range, in this case once I enter cl. it will show me a list of properties and methods that I can use. It makes it easy if you don’t remember the exact property or method name:
If I wanted to make the cell bold I would do the following:
cl.Font.Bold = true
I did not use the offset function here as I want the country field in column A to be highlighted and bolded. My entire sub procedure from start to finish looks as follows:
—————————————————————————————————————–
Sub highlight()
Dim cl As Range
Dim totalcount As Integer
Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)
totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
For Each cl In ws.Range(“A2:A” & totalcount)
If cl.Offset(0, 1) > 1000000 Then
cl.Interior.Color = vbYellow
cl.Font.Bold = True
End If
Next cl
End Sub
—————————————————————————————————————–
After running the above subprocedure this is what my table now looks like:
I can also use a counter to accomplish the same thing, in which case I don’t use the cl variable
—————————————————————————————————————–
Sub highlight2()
Dim totalcount As Integer
Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)
totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
counter = 0
For counter = 1 To totalcount
If ws.Range(“B” & counter) > 1000000 Then
ws.Range(“A” & counter).Interior.Color = vbYellow
ws.Range(“A” & counter).Font.Bold = True
End If
Next counter
End Sub
—————————————————————————————————————–
The above code will work in the exact same way as the previous, the only difference is the counter. You’ll see in the below examples why a counter is useful.
Suppose I wanted to delete the rows where the population was less than 1,000,000 people in 2015. I could modify my original code to work as follows:
—————————————————————————————————————–
Sub deleterow()
Dim cl As Range
Dim totalcount As Integer
Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)
totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
For Each cl In ws.Range(“A2:A” & totalcount)
If cl.Offset(0, 1) < 1000000 Then
cl.EntireRow.Delete
End If
Next cl
End Sub
—————————————————————————————————————–
The key difference is instead of bolding and highlighting I have the following line:
cl.EntireRow.Delete
This line will delete the row that the cell is on. If I run this subprocedure, I get the following result:
You’ll notice there is a problem. I have a country that has less than 1,000,000 people in this list – Antigua and Barbuda. It worked in a few instances – it got rid of Aruba, Andorra, and American Samoa. The problem is because American Samoa was right before Antigua and Barbuda. When I delete a row the loop does not factor in that the row that Antigua and Barbuda was on has now moved up a slot so when the loop goes to the next row it has skipped over the one that Antigua and Barbuda is on. So the loop fails in this instance when you run into consecutive rows meeting the condition you have specified.
However all you need to do to correct this is to start your loop backwards: start from the last row and work your way up. To do this I use the counter, The opening line of my loop is as follows:
For counter = totalcount To 2 Step -1
If you recall totalcount is my last row, and it will go until it reaches row 2. Step -1 indicates that it should go up one row at a time (if I wanted it go up two rows at a time it would be Step -2 instead). That way if I delete a row it doesn’t affect my loop since the data above the row I’ve deleted remains the same. My new sub procedure looks like this:
—————————————————————————————————————–
Sub deleterow()
Dim totalcount As Integer
Dim ws As Worksheet
Set ws = Worksheets(“Sheet1”)
totalcount = WorksheetFunction.CountA(ws.Range(“A:A”))
counter = 0
For counter = totalcount To 2 Step -1
If ws.Range(“B” & counter) < 1000000 Then
ws.Range(“A” & counter).EntireRow.Delete
End If
Next counter
End Sub
—————————————————————————————————————–
Now when I run the above sub procedure I get the following result:
Now you’ll notice Antigua and Barbuda is now gone and my list only contains countries with populations of more than 1,000,000 people.