When you are working with large datasets that contain blank rows, it can clutter your worksheet and hinder data analysis. While you can manually remove a small number of blank rows, it becomes time-consuming and inefficient when dealing with hundreds of blank rows. In this tutorial, we present six different methods to efficiently remove blank rows in batches. These techniques cover various scenarios you may encounter in Excel, allowing you to work with cleaner and more structured data.

• Remove blank rows
• By a helper column
• By Kutools in 3 seconds
• Manually
• By using VBA
• Remove rows containing blank cell(s)
• By the Go To Special feature
• In a key column by the Filter feature

Video: Remove blank rows

Remove blank rows

When removing blank lines from a dataset, it's important to exercise caution as some commonly suggested methods can accidentally remove rows that contain data. For example, two popular tips found on the internet are (which are also provided in this tutorial below):

• Using "Go To Special" to select blank cells and then removing the rows of these selected blank cells.
• Using the Filter feature to filter blank cells in a key column and then removing the blank rows in the filtered range.

However, both of these methods have the potential to mistakenly remove rows that contain important data as below screenshots shown.

To avoid such unintended deletions, it is recommended to use one of below four methods to precisely delete blank rows.

>> Remove blank rows by a helper column

Step 1: Add a helper column and use COUNTA function

1. In the most right of the dataset, add the "Helper" column and use below formula in the first cell of the column:

   =COUNTA(A2:C2)

   

   Note: In the formula, A2:C2 is the range that you want to count the nonblank cells.



2. Then drag auto fill handle down to fill the formula to count the number of nonblank cells in each row. "0" indicates the relative row is entirely blank.

   

Step 2: Filter the blank rows by the helper column

1. Click at any cell of the helper column, select Data > Filter.

   

2. Then click at the filter arrow and only check 0 in the expanded menu, and click OK.

   

   Now all empty rows have been filtered.

   

Step 3: Delete blank rows

Select the blank rows (click at the row number and drag down to select all blank rows), then right click to choose Delete Row from the context menu (or you can use shortcuts Ctrl + -).

Step 4: Select Filter in the Sort & Filter group to clear the applied filter

Result:

>> Remove blank rows by Kutools in 3 seconds

For a quick and effortless way to accurately eliminate blank rows from your selection, the best solution is to utilize the Delete Blank Rows feature of Kutools for Excel. Here is how:

1. Select the range that you want to remove blank rows from.
2. Click Kutools > Delete > Delete Blank Rows > In Selected Range.
3. Choose the desired option as you need and click OK in the popped dialog.

   

>> Remove blank rows manually

If there are few blank rows to remove, you can also manually remove them.

Step 1: Select blank rows

Click on the row number to select a single blank row. If there are multiple blank rows, hold the Ctrl key and click on the row numbers one by one to select them.

Step 2: Delete empty rows

After selecting the empty rows, right click and choose Delete from the context menu (or you can use shortcuts Ctrl + -).

Result:

>> Remove empty rows by using VBA

If you are interested in VBA, this tutorial provides two VBA codes for you to remove empty rows in selection and in the active worksheet.

Step 1: Copy VBA to the Microsoft Visual Basic for Applications window

1. Active the sheet that you want to remove blank rows from, then press Alt + F11 keys.

   

2. In the popping window, click Insert > Module.

3. Then copy and paste one of below codes to the blank new module.

   Code 1: Remove blank rows from the active worksheet

   Sub RemoveBlankRows()
   'UpdatebyExtendoffice
       Dim wsheet As Worksheet
       Dim lastRow As Long
       Dim i As Long
       
       ' Set the worksheet variable to the active sheet
       Set wsheet = ActiveSheet
       
       ' Get the last row of data in the worksheet
       lastRow = wsheet.Cells(wsheet.Rows.Count, 1).End(xlUp).Row
       
       ' Loop through each row in reverse order
       For i = lastRow To 1 Step -1
           ' Check if the entire row is blank
           If WorksheetFunction.CountA(wsheet.Rows(i)) = 0 Then
               ' If the row is blank, delete it
               wsheet.Rows(i).Delete
           End If
       Next i
   End Sub
   

   Code 2: Remove blank rows from selection

   Sub RemoveBlankRowsInRange()
   'UpdatebyExtendoffice
   Dim sRange As Range
   Dim row As Range
   ' Prompt the user to select a range
   On Error Resume Next
   Set sRange = Application.InputBox(prompt:="Select a range", Title:="Kutools for Excel", Type:=8)
   ' Check if a range is selected
   If Not sRange Is Nothing Then
   ' Loop through each row in reverse order
   For Each row In sRange.Rows
   ' Check if the entire row is blank
   If WorksheetFunction.CountA(row) = 0 Then
   ' If the row is blank, delete it
   row.Delete
   End If
   Next row
   Else
   MsgBox "No range selected. Please select a range and run the macro again.", vbExclamation
   End If
   End Sub
   

   

Step 2: Run the code and delete blank rows

Click Run button or press F5 key to run the code.

• If you use the code 1 to remove empty rows in the active sheet, after running the code, all blank rows in the sheet are going to be removed.

• If you use the code 2 to remove empty rows from selection, after running the code, a dialog pops out, select a range that you want to remove blank rows from in the dialog, then click OK.

  

Results:

Code1: Remove empty rows in the active sheet

Code2: Remove empty rows in the selection

Remove rows containing blank cells

There are two parts in this section: one is using Go To Special feature to remove rows containing blank cells, and the another one is using Filter feature to remove rows that have blanks in a specific key column.

>> Remove rows containing blank cells by the Go To Special

The Go To Special feature is widely recommended for removing empty rows. It can be a helpful tool when you need to remove rows that contain at least one blank cell.

Step 1: Select the blank cells in the range

1. Select the range you want to delete empty rows from, select Home > Find & Select > Go To Special.

   

   Or you can directly press F5 key to enable Go To dialog, and click Special button to toggle to Go To Special dialog.

2. In the Go To Special dialog, choose Blanks option and click OK.

   

   Now all empty cells in the selected ranged have been selected.

   

Step 2: Delete rows that contain blank cells

1. Right click at any selected cell, and select Delete from the context menu (or you can use shortcuts Ctrl + -).

   

2. In the Delete dialog, choose Entire row option and click OK.

   

Result:

>> Remove rows containing blank cells in a key column by the Filter feature

When you have a large dataset and want to remove rows based on a condition where a key column contains blank cells, Excel's Filter feature can be a powerful tool.

Step 1: Filter the blank cells in the key column

1. Select the dataset, click Data tab, go to Sort & Filter group, click Filter to apply filter to the dataset.

   

2. Click the filter arrow of the key column you want to remove rows based on, in this example, ID column is the key column, and only check Blanks from the expanded menu. Click OK.

   

   Now all blank cells in the key column have been filtered.

   

Step 2: Delete rows

Select the remainning rows (click at the row number and drag down to select all blank rows), then right click to choose Delete Row in the context menu (or you can use shortcuts Ctrl + -). And click OK on the popping dialog.

Step3: Select Filter in the Sort & Filter group to clear the applied filter

Result: