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

Your time is valuable. It's time to become a VBA expert.

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!

Discover how this Nuclear Engineer Mastered Excel VBA

And why you should, too

Your time is valuable. It's time to become a VBA expert.

About Ryan Wells

Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.