Remove Blank Rows in Excel Hack

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…

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("A1").Select

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)

Else

Set EmptyRows = i

End If

End If

Next

If Not EmptyRows Is Nothing Then

Application.ScreenUpdating = False

EmptyRows.Delete

Application.ScreenUpdating = True

End If

'De-selects the current selection

Range("A1").Select

End Sub

Suggestions

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: Suggestions@ReadySteadyXL.com