There are quite literally dozens of ways to delete blank rows in Excel. Some of them use VBA and some of them don’t. In this tutorial I’m only going to give you one solution. No sense in giving you option-overload!

The VBA macro in this guide deletes an entire row in Excel if the entire row is blank. In other words, if no data is shown in the row, the row is removed.

Excel VBA Delete Blank Rows

Sub DeleteBlankRows()
'DESCRIPTION: Delete an entire row in Excel if the entire row is blank.
'HOW TO USE:  Select the sheet you want to clean, then run this macro.
'DEVELOPER:   Ryan Wells (
Dim MyRange As Range
Dim MyRow As Range

Application.ScreenUpdating = False
Set MyRange = Selection.SpecialCells(xlCellTypeBlanks) 'select all blank cells
For Each MyRow In MyRange.Rows 'for each row with a blank cell
    If WorksheetFunction.CountA(MyRow.EntireRow) = 0 Then 'if no data in any column, then
        MyRow.EntireRow.Delete 'delete entire row
    End If
Next MyRow
Application.ScreenUpdating = True
End Sub

Write better macros in half the time
I want to help you write macros with my Excel VBA tutorials and VBA code examples. That’s why I developed this unique 3-part VBA training program to help you quickly learn VBA and gain access to my entire macro library without ever leaving Excel.

How to use the DeleteBlankRows Routine

I’ve included instructions and details in the comments of the macro itself so my commentary in this section is going to be limited. Basically, all you need to do is

  1. Select the worksheet you want to clean
  2. Open your VBA editor
  3. Insert a module
  4. Paste the macro above
  5. Run the macro (F5 from the VBA editor)

That’s really all there is to it. If the entire row is blank, the row will be deleted.

Reach out to my VBA Consulting page if you’re working on a macro, but you can’t quite figure out how to finish it. I’ll be happy to help you out!

Please subscribe to my email list and share this article with your friends on Facebook, Twitter, and Google+.

If you’re ready to free up your time in the office, visit my Excel Add-ins page for some great automation products. There are plenty more grab-and-go macro examples in my VBA Code Library so take a look there, too!

About Ryan Wells

Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA products, including Mouse To Macro. See more Excel products, including online courses and books, by visiting the Excel VBA Store.