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

Make powerful macros with our free VBA Developer Kit

It’s easy to copy and paste a macro like this, but it’s harder make one on your own. To help you make macros like this, we built a free VBA Developer Kit and wrote the Big Book of Excel VBA Macros full of hundreds of pre-built macros to help you master file I/O, arrays, strings and more - grab your free copy below.

I'll take a free VBA Developer Kit

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

That’s all for this tutorial. When you’re ready to take your VBA to the next level, subscribe using the form below.