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 (wellsr.com)
'----------------------------------------------------------------------
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 see people struggling with Excel every day and I want to help. That's why I'm giving away my personal macro library for free. This powerful gift lets you automatically import all my macros directly into your spreadsheets with just one click.

I want to join the free wellsrPRO VBA Training program

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 The VBA Tutorials Blog

Ryan Wells

The VBA Tutorials Blog was created by Ryan Wells, a Nuclear Engineer and professional VBA Developer. He created this site to help others learn to write macros in Excel. That's why he developed a unique 3-part free Excel training program to help others quickly learn VBA in a natural setting: right inside Excel.