4 easy ways to quickly remove Multiple Blank or empty rows in Excel

Removing multiple blank or empty rows in Excel is essential when cleaning up data before analysis, reporting, or presentation. Blank rows can disrupt data integrity, complicate calculations, and obscure insights. This guide introduces four straightforward methods to efficiently eliminate these empty spaces, ensuring your data remains continuous and easy to manage.

• Remove blank rows with the Go To Special command (4 steps)
• Easily remove blank rows in range/sheet/workbook with Kutools (1 step)
• Remove blank rows with the VBA code (4 steps)
• Remove blank rows with the helper column and Filter function (7 steps)
• Download sample file

Now please do as below steps to remove empty rows with the "Go To Special" feature:

1. Select the range you want to remove blank rows, click "Home" tab, then in "Editing" group, click "Find & Select" > "Go To Special".

2. In the "Go To Special" dialog, check "Blanks" option.

3. Click "OK", now all blank cells in the selection have been selected.

4. Click "Home" tab, in the "Cells" group, click "Delete" > "Delete Sheet Rows".

Now the rows with blank cells have been removed.

If you need a quick and error-free method to remove all blank rows from a selection, active sheet, selected sheets, or an entire workbook, consider using the "Delete Blank Rows" tool from "Kutools for Excel". This tool simplifies the task, enabling you to eliminate blank rows with just a single click, thus avoiding the use of more complex methods.

Click "Kutools" > "Delete" > "Delete Blank Rows", then in the sub drop-down list, choose one operation as you need.

1. Press "Alt" + "F11" key to enable "Microsoft Visual Basic for Applications" window.

2. Click "Insert" > "Module" to create a new "Module" script, copy and paste below code to the script.

VBA: Remove empty rows

Sub DeleteBlankRows()
'Update 20190107
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
xRows = WorkRng.Rows.Count
Application.ScreenUpdating = False
For I = xRows To 1 Step -1
    If Application.WorksheetFunction.CountA(WorkRng.Rows(I)) = 0 Then
        WorkRng.Rows(I).EntireRow.Delete XlDeleteShiftDirection.xlShiftUp
    End If
Next
Application.ScreenUpdating = True
End Sub

3. Press "F5" key to run the code, then select a range you use to remove blank rows in the popping "KutoolsforExcel" dialog.

4. Click "OK". Now the empty rows in selection have been removed.

1. Insert one column before your data range, take inserting Column A as instance, then type the following formula into A3 to combine data in row3, see screenshot:

=B3&C3&D3

Tip: If your data across Column B3 to Column N, adjust the formula to =B3&C3&D3&E3&…&N3.

2. Then fill the formula to cells by dragging the auto fill handle down.

3. Then click "Data" > "Filter".

4. Then click the "Filter icon" at the helper column, check "Blanks" option only in the drop-down list. See screenshot:

5. Click "OK", the all blank rows have been filtered out.

6. Select the filtered rows, click "Home" > "Delete" > "Delete Sheet Rows" to remove the blank rows.

7. Then click "Data" > "Filter" again to disable "Filter" function.

Click to download sample file