Remove blank rows in Excel with this VBA code

 Jul 17, 2014

Quite often, you will find that you may have several table ranges in your Excel worksheet separated by blank rows that are undesirable. You also might even have a big table that has a lot of unwanted blank rows in it which you want to remove so you can condense the table. Although you can select consecutive rows and delete them in Excel itself, you may not really want to do this when they are non-consecutive and spread out. This is because firstly, it is time consuming and secondly, you’ll be prone to making mistakes by selecting non-empty rows or leaving one or two out. Wouldn’t it be nice to click a button to remove all those blank rows? You can achieve this through Excel VBA and today, I’m going to explain how. The steps I am taking to develop the program are as follows:
  1. Select a cell in the first range and press ‘Ctrl+A’ on the keyboard to select the whole range.
  2. Note down the last row of this range and call it ‘LastRow_Range1.’
  3. Starting from this last row, move down all the blank rows until you hit a non-blank row. This indicates the vicinity of the second range.
  4. Select this non-blank cell and press  ‘Ctrl+A.’
  5. Record the first row of this range and call it ‘FirstRow_Range2.’
  6. Now all the rows between ‘LastRow_Range1’ and ‘FirstRow_Range2’ can be deleted.
  7. Now the two ranges are one and so we can repeat these steps to get rid of other blank cells in between ranges.
However, we need to know when to stop this process. One easy way is to check if we have gone over the row that holds the ‘LASTCELL.’ LASTCELL is a worksheet property that indicates the bottom right corner of the region that has some data in it. When you save a workbook, Microsoft Excel stores only the part of each worksheet that contains data or formatting.
Sub RemoveBlanks()

Dim endRow, lastRow_Range1, firstRow_Range2, r As Integer endRow = Cells(1, 1).SpecialCells(xlCellTypeLastCell).Row firstRow_Range1 = ActiveCell.CurrentRegion.Range(“A1”).Row lastRow_Range1 = ActiveCell.CurrentRegion.Rows.Count + firstRow_Range1 – 1 Do While lastRow_Range1 <= endRow

r = lastRow_Range1 + 1 Do While WorksheetFunction.CountA(Rows(r)) = 0

If r > endRow Then Exit Sub r = r + 1

Loop i = 1: Do While Cells(r, i) = “”: i = i + 1: Loop firstRow_Range2 = Cells(r, i).CurrentRegion.Range(“A1”).Row Rows(lastRow_Range1 + 1 & “:” & firstRow_Range2 – 1).Delete lastRow_Range1 = ActiveCell.CurrentRegion.Rows.Count + firstRow_Range1 – 1

Loop

End Sub
So let’s take a closer look at this piece of code. The first line is the variable declaration and the second line stores the row of the LASTCELL of the worksheet. To get the last row of the first range, we need to get its first row then add it to the total number of rows in the range minus 1. To do that, we issue a ‘Ctrl+A’ equivalent in VBA by using ‘ActiveCell.CurrentRegion.’ Then we get the row of its first cell ‘A1.’ Note that ‘A1’ is the first cell of the selected range. So the first row is ‘ActiveCell.CurrentRegion.Range(“A1”).Row’ and the last row is ‘ActiveCell.CurrentRegion.Rows.Count + firstRow_Range1 – 1.’ Then it enters a WHILE loop which keeps running as long as the row we are working on is less than the LASTCELL (endRow). The next few lines of the code scan down the rows from the last cell of ‘Range1’ to find the next non-blank cell.
r = lastRow_Range1 + 1 Do While WorksheetFunction.CountA(Rows(r)) = 0

If r > endRow Then Exit Sub r = r + 1

Loop
This is an indication of the start of range 2. To check whether an entire row is blank, I have used ‘WorksheetFunction.CountA(Rows(r))’ to compare it with zero. If there is any non-blank cell in that row, the ‘CountA’ wouldn’t be zero. After the loop, we would have found the first non-blank row, but it is important to check which cell in this row has a value, because we need to issues Ctrl+A on this cell to select its entire range. This is for a situation when there is a table which does not start from column 1 or 2 but say, from column 3. Therefore, the next line is a complete WHILE loop that scans all the cells in the row to find the first non-blank cell and it records its column number in variable i. Now, the next line issues Ctrl+A to select the whole range 2 and records its first row in firstRow_Range2 variable. We’ve now got the ‘LastRow_Range1’ and ‘FirstRow_Range2,’ so we can delete all rows in between which is done by the next line in the loop, that is, ‘Rows(lastRow_Range1 + 1 & “:” & firstRow_Range2 – 1).Delete.’ At this point, the blank rows between the two ranges have been removed and the two ranges are now one. The last line in the loop grabs the last row of this new collective range to prepare for the next iteration of the loop. Remember, for this program to work you have to select any cell in the first range in your worksheet before you run the program, because it all depends on ActiveCell in the code. Therefore, if you want to leave the blank rows for the first 2 or 3 tables but remove those from say, table 4 downwards then select a cell in table 4 before running the program.

How do your Excel skills stack up?   

Test Now  

About the Author:

Cyrus Mohseni  

Having worked within the education and training industry for over 15 years as well as the IT industry for 10 years, Cyrus brings to New Horizons a wealth of experience and skill. Throughout his career he has been involved in the development and facilitation of numerous training programs aimed at providing individuals with the skills they require to achieve formal qualification status. Cyrus is a uniquely capable trainer who possesses the ability to connect with students at all levels of skill and experience.

Read full bio
top