Aug 08, 2014

Quite often, you’ll be required to remove non-contiguous blank rows to join ranges of data together. This is quite tricky to do in Excel, but I’ve explained how to do this is in one of my previous posts, Remove blank rows in Excel with this VBA code. Now, what if you want to do this directly in Excel VBA? In this article, I’ll provide you with the piece of code that you’ll need to use in VBA, and explain each component in detail. Here’s the code to remove blank rows:

Sub RemoveBlankRows()          If Selection.Rows.Count < 2 Then               MsgBox “You should select a range with at least 2 rows!”               Exit Sub          End If Dim myrange, rw As Range Set myrange = Selection          For i = 1 To myrange.Rows.Count               Set rw = myrange.Rows(i)               If WorksheetFunction.CountA(rw) = 0 Then                             rw.Delete                             i = i – 1 End If          Next i End Sub

The code in red is simply a test to see if the user has selected a range of at least 2 rows. Otherwise, the program will stop. Of course, it doesn’t make sense to remove blank rows from a single row. It then declares a variable ‘myRange’ of type range and set it to be the selected range. From now on, the program should start from the first row of myRange and go down to check if a row is blank. If so, it deletes it. This is done by the ‘For’ loop in green, which increments the counter i from 1 to the number of rows in myRange. To check if a row is blank, we use ‘CountA’ over this row. If there is any data in any of the cells, CountA returns a non-zero value. However, we need to be cautious with the deletion. When a row is deleted, the range automatically shrinks and the counter i, which was referring to the current row, is now referring to the next one. Hence, when the loop goes on to the next iteration, it increments again and therefore, the loop misses to check the actual next row and goes 2 rows down. That’s why we have decremented i in the line (i = i – 1) after a deletion. Remember, to use this program, you need to initially select a range from which you want to remove blank rows. Also, this selection should not end in blank rows otherwise, the range won’t shrink and the program goes to a loop. To avoid this situation you can add these two line to the code around the line “rw.Delete “ in blue, as per below:

rowsCount = myrange.Rows.Count rw.Delete If myrange.Rows.Count = rowsCount Then Exit For

This will check if the range is shrinking. If it is not shrinking then it means it has hit the last blank row and the program should stop. So that’s all there is to it, good luck and happy coding!