top of page

Remove Blank Rows in Excel Hack

Updated: Feb 24, 2020

A long time ago, in a land far far away, I was working on a monthly report. I was given the task of formatting a data dump (.CSV file) downloaded from an application. This client report needed to be formatted to meet the client’s needs, it involved splitting columns, transposing tables, adding formulas and deleting blank rows. The task was supposed to be a one-off so I never created a macro for it at the time.

The most painful step when formatting this report was deleting the blanks rows, because there were loads of them in this huge report. At first, I was going through the report looking for these rows and deleting them using this method…

Right click method

I would Right-click on a row and select Delete or use Ctrl and Click to select multiple rows.

As you can imagine, this proved time consuming and mind numbing when working with the large spreadsheet. There would be times when I would click on the wrong row or cell, then had to start all over again. I thought there must be an easier way to do this…

Then I discovered these two hacks (No VBA or Macro required):

Find and Select

Let's find those blank rows.

1. Select any cell within your data.

2. Under the Home tab, in the Editing group, click Find & Select and select Go To Special

3. Select Blanks and click OK

4. All the blank rows will be highlighted

5. Right-click on one of the highlighted cells (not a row number) and select Delete

6. In the Delete dialogue box, select Entire Row.

Please Read: You need to use this last step with caution. If there are any blank cells in your spreadsheet, this step will also delete their row. If you’re confident that your spreadsheet will only have blank rows then go ahead. Otherwise, move to the next hack…

Filter the blanks

This is probably the safest way to delete empty rows in your spreadsheet. Let’s have a look at the steps.

1. First select all your data in the spreadsheet to ensure the Filter picks up everything. To do this, select the first cell in your spreadsheet and then using your keyboard, press Ctrl, Shift and End.

2. Now switch the Filter on by pressing Ctrl, Shift and L on your keyboard. This is much quicker than going to the ribbon.

3. For each column select Blank from the Filter, until there only blank rows remaining.

4. Select the rows from the filter results.

5. Right-click and select Delete Row. All the blank rows will now be deleted.

6. Remove the Filter by pressing Ctrl, Shift and L on your keyboard.

This is the safest way to delete the blank rows. If you’re looking for a more automated method, then you need a macro…


Please Read: Use this macro with caution. Once the macro has been run you will not be able to Undo.

This macro will only delete the blank rows in your spreadsheet. In case you didn't read the above, you Can Not Undo the actions after this macro has worked its magic.

For more information on working with Macros, visit the Microsoft website for the steps.

The code:

Sub RemoveEmptyRowsOnly()

'Deletes rows that are completely empty in the selected Worksheet

Dim i As Range

Dim EmptyRows As Range

'Select all the data in the current Worksheet


Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

'Checks if each row is complete empty before deleting

For Each i In Selection.Rows.EntireRow

If WorksheetFunction.CountA(i) = 0 Then

If Not EmptyRows Is Nothing Then

Set EmptyRows = Union(EmptyRows, i)


Set EmptyRows = i

End If

End If


If Not EmptyRows Is Nothing Then

Application.ScreenUpdating = False


Application.ScreenUpdating = True

End If

'De-selects the current selection


End Sub


I'm always looking for new ideas for topics. If you have a suggestion you would like me to cover or think would be useful, then please email:

61 views0 comments


Sign up circies (3).png

Tony Laskowski

Applications Trainer & Digital Creator

Ready Steady Excel was set up by Tony to help a wider audience to achieve more with Microsoft 365.

Join the Mailing List to keep on track!


Discover More!

100% Free Sign Up - Unsubscribe Anytime!

bottom of page