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)
• DowDnload sample file

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

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.

Before using this tool, you need to free installing Kutools for Excel firstly.

Kutools for Excel

60-Day Free Trial 300 Advanced Tools Solve 80% Excel Problems Improve 70% Working Efficiency Be An Excel Expert, Gain People's Recognition

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 this formula =B3&C3&D3 into A3 to combine data in row3, see screenshot:

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