VBA: Remove Blank Rows

December 20th, 2004 | Categories: Cells, Columns & Rows | Tags: , ,

The obvious way to remove blank rows from a data set is to simply sort the data. This moves the blank rows to the bottom of the data and “removes” them. But what if you want the blank rows removed, however you don’t want the data sorted? VBA.

The following macro will remove blank rows without any sorting (turn off screen updating to go faster).

Sub DeleteBlankRows()
Dim x As Long

With ActiveSheet
    For x = .Cells.SpecialCells(xlCellTypeLastCell).Row _
        To 1 Step -1

        If WorksheetFunction.CountA(.Rows(x)) = 0 Then
            ActiveSheet.Rows(x).Delete
        End If

    Next
End With

End Sub

Get LIVE Help
Custom Excel solutions & live Excel help!
► Go to www.ExcelAutomationHelp.com
► Email Now@ExcelAutomationHelp.com
  1. December 21st, 2004 at 13:27
    Reply | Quote | #1

    Concerning the possibility of data that might have been entered and then deleted . . . I’ve found that some Excel users will delete data using the space bar as opposed to the delete key. They like to use the spacebar because they can hit it with their left hand while hitting the enter key with their right to quickly move down a column. If the space bar is used, the cell looks blank but really is not. Although the macro is technically correct, the novice user might interpret the macro as failing if he or she uses a spacebar to delete data and then runs the macro.

    A test of this would be to enter the letters A, B, C, D, E, and F in the cell range A1:A6. Go to cell A3 and delete the C using the spacebar. Then go to cell A5 and delete the E using the spacebar. Run the macro and nothing happens. However, if you were to use the delete key the macro works fine.

    A possible alternative that covers both bases might be to use a macro that autofilters the data and then deletes the blank rows.

  2. Mark
    December 21st, 2004 at 18:50
    Reply | Quote | #2

    Thanks for the tip John, I wasn’t aware of the “spacebar delete”.

    Autofilter fails when the dataset is lacking headers, and you can’t TRIM a row…scratching head…

  3. November 10th, 2010 at 13:48
    Reply | Quote | #3

    use replace space bar with empty (“”) before looping.