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.

2018 Update: I’ve added a more efficient way to delete blank rows using VBA. If your spreadsheet has more than a couple dozen blank rows, check out the DeleteBlankRows2 macro near the bottom of this tutorial. This VBA delete blank rows solution was shared by a commenter, PG CodeRider, so give him a big thank you in the comments!


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.


2018 Update: Faster way to Delete Blank Rows

A commenter with the handle PG CodeRider stopped by and suggested using the Union function to store all the blank rows and delete them all at once near the end of the VBA macro. I thought this was a great idea since it’s a much more efficient way to delete blank rows with VBA for spreadsheets with hundreds of blank lines.

The code is a bit longer, but that’s okay since it’s also a tad faster. You likely won’t notice the difference for smaller spreadsheets, but try it out and let me know what you think!

Sub DeleteBlankRows2()
'----------------------------------------------------------------------
'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: PG CodeRider (commenter on wellsr.com)
'----------------------------------------------------------------------
Dim ClearRng As Range
Dim MyRange As Range
Dim MyRow As Range

Application.ScreenUpdating = False
Set MyRange = Selection.SpecialCells(xlCellTypeBlanks) 'select all blank cells
Set ClearRng = Rows(ActiveSheet.Rows.Count) 'used to avoid having to create an if statement for first union
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
        Set ClearRng = Union(ClearRng, MyRow.EntireRow)
    End If
Next MyRow
ClearRng.Delete (xlUp) 'executing the delete after loop finishes saves incredible overhead
Application.ScreenUpdating = True
End Sub

Please subscribe to my free wellsrPRO VBA Training Program 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 and VBA Cheat Sheets. There are plenty more grab-and-go macro examples in my VBA Code Library so take a look there, as well!


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.