3 Ways to Delete Entire Blank Rows in Excel + Video Tutorial

Bottom line: Learn a few ways to delete blank rows in Excel from a data set or table. Includes video tutorial and VBA macro.

Skill level: Beginner

Video Tutorial

Download the File

Download the example Excel file to follow along.  The file also contains the Select Blank Rows macro.

3 Ways to Find & Delete Blank Rows

If you are exporting reports from any type of system then there is a good chance the data contains blank rows.  One very common task we do in Excel is to delete these blank rows from the worksheet, especially if you are preparing your data for use with a pivot table.

In this case we want to delete rows that are entirely blank.  If a row contains a few blank cells, we still want to keep that row of data.

There are several ways to accomplish this task.  Let's look at some ways to save time and make this process easy.

#1 – Filter for Blanks

If our data set contains a column that ONLY has blank cells in the blank rows, then we can filter for blanks on that column.

In our example file we can filter the Customer column for blanks.  We can see that rows 4 & 9 still contain data in the other columns.  Therefore we do NOT want to delete these rows.

You can continue to filter other columns for blanks until all cells in the range or Table are blank.  However, this can be time consuming if you have a lot of columns in your data set.

Once you have a filter applied that makes only the entire blank rows visible, then select and delete the rows.  The rows can be deleted by pressing the Delete button on the Home tab of the ribbon (keyboard shortcut: Ctrl+-)

Checkout my 3-part video series on Excel Filters to learn more time saving shortcuts.

#2 – Use a Formula to Identify Entire Blank Rows

Another approach is to use a formula with the COUNTA function.  The COUNTA function returns the count of cells that are NOT empty in a range.

We can add this formula to the right of the table, and copy down to each row in the table.

This will give us a column of values that returns the number of empty or non-blank cells in the row.  If the formula returns a 0 (zero) then all the cells in that row are blank.

We then filter the new column for 0 (zero).  This will show all of the entire blank rows, and hide all non-blank rows.

Next we just select and delete the visible rows.

To recap, the steps to delete entire blank rows are:

1. Add a column with the COUNTA formula to count non-blank cells.
2. Filter the column for 0 (zero).
3. Select all visible rows in the filter range.
4. Delete the rows with the Ctrl+- keyboard shortcut.
5. Clear the filter to view all rows.

I use an Excel Table (Beginner's Guide to Tables) in this example, and highly recommend using Tables.  You can turn off the Table Formulas (structured references) if you don't like them.

#3 – Use a Macro to Select or Delete Entire Blank Rows

The fastest approach to deleting blank rows is to use a macro.  This allows us to perform the entire task with a click of a button.

The following macro will select all the entire blank rows in the selected range.  If you only have a single cell selected, the macro finds all blank rows in the UsedRange of the active sheet.

Sub Select_Blank_Rows()
'Select all entire blank rows in selected range
'Source: https://www.excelcampus.com/tips/delete-entire-blank-rows/

Dim rRow As Range
Dim rSelect As Range
Dim rSelection As Range
 
  'Check that a range is selected
  If TypeName(Selection) <> "Range" Then
    MsgBox "Please select a range first.", vbOKOnly, "Select Blank Rows Macro"
    Exit Sub
  End If
  
  'Check that multiple cells are selected
  If Selection.Cells.Count = 1 Then
    Set rSelection = ActiveSheet.UsedRange
  Else
    Set rSelection = Selection
  End If
 
  'Loop through each row and add blank rows to rSelect range
  For Each rRow In rSelection.Rows
    If WorksheetFunction.CountA(rRow) = 0 Then
      If rSelect Is Nothing Then
        Set rSelect = rRow
      Else
        Set rSelect = Union(rSelect, rRow)
      End If
    End If
  Next rRow
  
  'Select blank rows
  If rSelect Is Nothing Then
    MsgBox "No blank rows were found.", vbOKOnly, "Select Blank Rows Macro"
    Exit Sub
  Else
    rSelect.Select
  End If
  
End Sub

The macro only selects the blank rows. We can then manually delete the rows with the Ctrl+- keyboard shortcut or pressing the Delete button on the Home tab of the Ribbon.

You can replace the following line of code:

rSelect.Select

with this line of code if you would rather have the macro delete the blank rows.

rSelect.Rows.Delete Shift:=xlShiftUp

It's important to note that you will NOT be able to undo the delete action. So I prefer to have the macro just select the blank rows, and then delete them manually. That way I can undo the action if needed.

If you do add the code to delete the row, I'd recommend adding a Yes/No Message Box to the macro to prevent you from running it accidentally.

The macro can also be modified to select blank columns instead of rows.

Change the following line of code:

For Each rRow In rSelection.Rows</pre>

To:

For Each rRow In rSelection.Columns

The macro will loop through all the columns and select the blank columns in the selected or used range.  Thanks to Sandy for suggesting this!

The Select Blank Rows macro can be added to your Personal Macro Workbook (video guide to creating your Personal Macro Workbook).  We can also add a custom macro button to the ribbon to quickly run the macro on any open workbook.

Other Methods to Delete Blank Rows?

One other method is to use the Go To Special window to select blanks.  We can use this instead of filtering a column for blanks.

Here are the steps to use the Go To Special window to select and delete blank rows:

1. Select an entire column in the worksheet.  The column must ONLY contain blank cells where the entire row is blank.
2. On the Home tab, click the Find & Select button, then choose Go To Special… (keyboard shortcut: F5, Alt+S)
3. Select the Blanks radio button on the Go To Special window. (keyboard shortcut: k, Enter)
4. Delete the entire row.  Home tab > Delete (keyboard shortcut: Ctrl+-)

It's important to note that this method suffers from the same potential as solution #1.  If the column contains blank cells where the entire row is NOT blank, then this will not work.

What other methods do you use for deleting blank rows?  Please share by leaving a comment below.  Thank you! 🙂