4 Simple Ways to Delete or Remove Blank Rows in Excel

Got a large Excel spreadsheet that is full of empty rows? How to quickly remove all the blank rows that are scattered everywhere in your Excel worksheet? Deleting each blank row manually can be very annoying and time-consuming. This tutorial will show you 4 simple methods to quickly delete or remove blank rows from your Excel spreadsheet in Excel 2016 / 2013 / 2010 / 2007.

Method 1: Delete Blank Rows in Excel with Go To Special Command

1. Highlight the area of your Excel spreadsheet in which you want to delete the blank rows. Under the Home tab, click Find & Select and then click Go To Special.

   

2. Choose the Blanks radio box and click OK.

   

3. You should see that all the empty rows in Excel are now highlighted in blue. Navigate to the Home tab, click Delete and choose Delete Sheet Rows. Now all the empty rows will be deleted.

   

Method 2: Delete Blank Rows in Excel Using Find & Replace

1. Open the Excel spreadsheet where you wish to delete blank rows. Select your data range and press Ctrl + F keyboard shortcut to open the "Find and Replace" dialog.

2. Click Options to see advanced search criteria. Leave the "Find what" field as empty, select Values under the Look in drop-down list and click Find All.

   

3. Click on one of the results that appear below the search box, then press Ctrl + A to select all the blank cells.

   

4. Close the "Find and Replace" dialog. From the Home tab, click Delete and then select Delete Sheet Rows from the drop-down menu.
5. All the empty rows you selected will be deleted immediately.

Method 3: Delete Blank Rows Using Excel Filter

1. Highlight just the range of data containing the blank cells. Navigate to the Home tab, click Sort and Filter and then select Filter.

   

2. Each column header will display an arrow button. Click on the arrow of the column that you wish to filter. Uncheck all the boxes except the Blanks checkbox. Click OK.

   

3. This will select all the bank rows at once. Navigate to the Home tab, click Delete and select Delete Sheet Rows. It will remove all your blank rows.

Method 4: Delete Blank Rows in Excel with VBA Macro

How to quickly delete blank rows using Excel VBA macro? This should be the fastest way to remove blank rows from your Excel spreadsheet.

1. Open your workbook in Excel. Press Alt + F11 to open Visual Basic Editor (VBE).
2. Right-click on your workbook name in the left pane, and select Insert -> Module from the context menu.

3. Copy the following VBA code and paste it to the right pane of the VBA editor.

   Sub remove_empty_lines()
   Dim start_time, arr(), rng, row_num As Long, column_num As Long, i, j
       Application.ScreenUpdating = False
       row_num = ActiveSheet.UsedRange.Rows.Count
       column_num = ActiveSheet.UsedRange.Columns.Count
       ReDim arr(1 To row_num)
       rng = ActiveSheet.UsedRange
       start_time = Timer
       For i = 1 To row_num
          For j = 1 To column_num
           If rng(i, j) <> "" Then arr(i) = i
        Next j, i
       With Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Column + column_num).Resize(row_num, 1)
       .Value = WorksheetFunction.Transpose(arr)
       ActiveSheet.UsedRange.Sort Key1:=Cells(ActiveSheet.UsedRange.Row, ActiveSheet.UsedRange.Column + column_num)
       .Value = ""
      End With
       Application.ScreenUpdating = True
       MsgBox Format(Timer - start_time, "0.00s")
   End Sub

   

4. Run the VBA code. Voila! The empty rows are gone.