6 Ways to Remove Blank Rows in Excel (Step-by-Step)

Clearing out blank rows in Excel can make your data more concise and organized. Here are six straightforward methods to remove blank rows:

Method 1: Using Filters

1. Click on the filter icon in any of the column headers.
2. In the filter dropdown, uncheck the box next to (Blanks) to show only non-blank rows.
3. Select the visible rows by clicking on the row numbers (left side) to highlight them.
4. Right-click on the selected rows and choose Delete.
5. Confirm the deletion by clicking Delete Sheet Rows.

Method 2: Using Go To Special

1. Press Ctrl + G or F5 to open the Go To dialog box.
2. Click on the Special button.
3. Choose Blanks and click OK.
4. The blank cells will be selected.
5. Right-click on any selected cell and choose Delete.
6. Confirm the deletion by clicking Delete Sheet Rows.

Method 3: Using VBA Macro

1. Press Alt + F11 to open the VBA Editor.
2. Click Insert > Module to insert a new module.
3. Copy and paste the following VBA code:

Sub RemoveBlankRows()
    Dim ws As Worksheet
    Dim rng As Range
    Dim i As Long

    Set ws = ActiveSheet
    Set rng = ws.UsedRange

    For i = rng.Rows.Count To 1 Step -1
        If Application.WorksheetFunction.CountA(rng.Rows(i)) = 0 Then
            rng.Rows(i).Delete
        End If
    Next i
End Sub

4. Press F5 to run the macro. It will remove all blank rows in the active sheet.

Method 4: Using Excel Table (ListObject)

1. Convert your data range into an Excel Table by selecting any cell within the range and pressing Ctrl + T.
2. Click the filter dropdown arrow in any column header.
3. Uncheck the box next to (Blanks) to display only non-blank rows.
4. Right-click on any selected row header and choose Delete.

Method 5: Using Power Query

1. Select your data range.
2. Go to the Data tab on the ribbon.
3. Click on From Table/Range to open Power Query Editor.
4. In Power Query, filter out blank rows.
5. Click Close & Load to update your Excel worksheet.

Method 6: Using Remove Duplicates

1. Select your data range.
2. Go to the Data tab on the ribbon.
3. Click on Remove Duplicates.
4. Check the box next to all columns to remove duplicates (including blank rows).
5. Click OK to remove duplicates and blank rows.