Bottom line: Learn a few ways to delete entire blank rows from a data set or table in this article and video.
Skill level: Beginner
Download the File
Download the example Excel file to follow along. The file also contains the Select Blank Rows macro.
Delete Blank Rows.xlsm (29.5 KB)
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:
- Add a column with the COUNTA formula to count non-blank cells.
- Filter the column for 0 (zero).
- Select all visible rows in the filter range.
- Delete the rows with the Ctrl+- keyboard shortcut.
- Clear the filter to view all rows.
#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:
with this line of code if you would rather have the macro delete the blank rows.
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:
- Select an entire column in the worksheet. The column must ONLY contain blank cells where the entire row is blank.
- On the Home tab, click the Find & Select button, then choose Go To Special… (keyboard shortcut: F5, Alt+S)
- Select the Blanks radio button on the Go To Special window. (keyboard shortcut: k, Enter)
- 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! 🙂