The article 5 ways to delete blank rows in Excel demonstrates several methods for deleting blank rows manually. But there’s a sixth way: You can use a macro. Regardless of how you acquire blank rows in data sets, it’s best to remove them. Most of Excel’s selection-based features interpret a blank row as the end of a data range; consequently, those features won’t work as expected if you leave blank rows in your data set. In this article, I’ll show you a macro that does the job for you.

I’m using Office 365 (Excel 2016 desktop) on a Windows 10 64-bit system. You can’t run macros in the browser edition. For your convenience, you can download the demonstration .xls, .xlsx, and .cls files. Don’t try to copy the code from this web page; the Visual Basic Editor (VBE) will complain about phantom characters it can’t interpret. Instead, import the .cls file from the download into your workbook. NOTE: Be careful when deleting entire rows. There might be data off screen that you don’t see.

This macro, in one form another has been around for a long time. If I knew the originator, I’d thank them and offer credit where due.

SEE: 3 handy Excel data entry shortcuts (free TechRepublic PDF)

The macro

First, let’s review the code shown in Listing A. If it looks complicated, don’t worry. The logic is simple. After declaring and setting a few variables, a For loop checks every row in the selected range for blank rows. When it finds one, the loop deletes the row and shifts the remaining rows up.

Listing A

Sub DeleteBlankRows()
'Delete blank rows in a selected range.
'Choose appropriate delete statement in For loop.
'Entire Row deletes entire rows.
'Delete deletes partial rows.

Dim rng As Range
Dim selectedRng As Range
Dim iRowCount As Integer
Dim iForCount As Integer

'Error handling; for good measure.
On Error Resume Next

Set selectedRng = Application.Selection
'Type:=8 argument specifies a Range object; input value must be a range.
Set selectedRng = Application.InputBox("Range", , selectedRng.Address, Type:=8)

Application.ScreenUpdating = False

'Count of rows in selected range used as For stop value.
'WorksheetFunction.CountA counts the number of cells that are NOT empty.
iRowCount = selectedRng.Rows.Count
For iForCount = iRowCount To 1 Step -1
If Application.WorksheetFunction.CountA(selectedRng.Rows(iForCount)) = 0 Then
'Delete entire row.
selectedRng.Rows(iForCount).EntireRow.Delete
'Delete partial row.
'selectedRnd.Rows(iForCount).Delete
End If
Next

Application.ScreenUpdating = True

End Sub

Some of the commands probably need a bit of explanation. First up is the InputBox function:

Set selectedRng = Application.InputBox(“Range”, , selectedRng.Address, Type:=8)

You’re probably already familiar with the InputBox function as a tool for soliciting data from the user. In this case, the user must specify a range because the argument, Type:=8, specifies that the input is a Range object.

The statement iRowCount = selectedRng.Rows.Count returns the number of rows in the input range (from the input box). The For loop uses this variable (minus 1) as its stop value.

The first statement in the For loop is an If statement. The CountA function counts the number of cells in the current row that aren’t empty. When the number of cells that aren’t empty is 0, the next statement deletes the entire row. The listing includes two delete statements: one that deletes the entire row and one that deletes partial rows within the selected range. Simply comment out the delete statement you don’t want. Or you might want to create two macros for more flexibility: one that deletes entire rows and one that deletes partial rows only.

Now that you know how the macro works, you’re ready to add it to your workbook. First open the VBE by pressing Alt+F11. In the Project Explorer, access the ThisWorkbook module and enter the code (or import from the downloadable .cls file). Next, return to Excel and save it as a macro-enabled file as follows :

  1. Click the File tab.
  2. Choose Save As from the left pane.
  3. From the File Type dropdown (below the Name control), choose Excel Macro-Enabled Workbook (*.xlsm).
  4. Click Save.

With the macro in place, you’re ready to test it.

SEE: Microsoft Azure: An insider’s guide (free TechRepublic PDF)

Using the macro

To demonstrate how to use the macro, we’ll delete the blank rows in the simple sheet shown in Figure A. There are lots of ways to run a macro, but we’ll use the Developer tab. If it’s not accessible, click the QAT dropdown, choose More Commands, select Customize Ribbon in the left pane, check Developer in the Main Tabs list, and click OK.

Figure A

Run the macro to delete the two blank rows in this simple sheet.

We’ll run the macro via the Developer tab as follows:

  1. Click the Developer tab.
  2. In the Code group, click Macros.
  3. In the resulting dialog, select ThisWorkbook.DeleteBlankRows and click Run.
  4. The macro prompts you to enter the working range, as shown in Figure B. If you select the range before running the macro, the input box will default to the selected range–how nice! However, you can highlight the range or enter it manually as well using the input box. You can also enter a range name or a Table object name.
  5. Click OK and you’re done. As you can see in Figure C, the blank rows are gone.

Figure B

Select the range before running the macro or input the range via the input box.

Figure C

The macro removes the blank rows.

Important to know

To use this macro efficiently, you should keep a few things in mind. First, you can’t undo the delete. My best advice is to always save any workbook before deleting data. That way, if you do make a mistake you’re okay. Second, if you’re working with a large data set, selecting the range might be awkward. Fortunately, the macro can handle a range name or a Table object name.

You might have noticed that some of the cells are blank for some of the records. The macro won’t delete incomplete records; it deletes only blank rows , as determined by the selected range.

If you use this macro a lot, consider adding it to the QAT. Read How to add Office macros to the QAT toolbar for quick access to learn how.

Send me your question about Office

I answer readers’ questions when I can, but there’s no guarantee. Don’t send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible. For example, “Please troubleshoot my workbook and fix what’s wrong” probably won’t get a response, but “Can you tell me why this formula isn’t returning the expected results?” might. Please mention the app and version that you’re using. I’m not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. You can contact me at susansalesharkins@gmail.com.

Also read…