Skip to main content

6 easy ways to remove blank rows in Excel (Step by step)

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.

doc remove blank rows 1


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.

doc remove blank rows 2

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.

    doc remove blank rows 5

  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

doc remove blank rows 10

Result:

Note: If you do not need the helper column any more, remove it after filtering.

>> 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.

Additional Information:
  • In addition to removing blank rows within a selection, Kutools for Excel also allows you to conveniently delete blank rows from the active worksheet, selected sheets, or even the entire workbook with just a single click.

  • Before using Delete Blank Rows feature, please install Kutools for Excel. Click here to download and have a 30-day free trial.


>> 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.

    doc remove blank rows 27

  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
    

    doc remove blank rows 27

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.

    doc remove blank rows 12

    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.

    doc remove blank rows 13

    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.

    doc remove blank rows 16

Result:

Note: As you see above, as long as the row contains at least one blank cell, it will be removed. This may cause some importance data loss. If the dataset is huge, you may need to take mass time to find the loss and restore. So, before using this way, I recommend you to take a backup firstly.

>> 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

doc remove blank rows 22

Result:

Note: If you want to remove blank rows based on two or more key columns, repeat Step 1 to filter blanks in the key columns one by one, then delete the remaining blank rows.

Best Office Productivity Tools

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates   |  Delete Blank Rows   |  Combine Columns or Cells without Losing Data   |   Round without Formula ...
Super Lookup: Multiple Criteria VLookup    Multiple Value VLookup  |   VLookup Across Multiple Sheets   |   Fuzzy Lookup ....
Advanced Drop-down List: Quickly Create Drop Down List   |  Dependent Drop Down List   |  Multi-select Drop Down List ....
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  |  Compare Ranges & Columns ...
Featured Features: Grid Focus   |  Design View   |   Big Formula Bar    Workbook & Sheet Manager   |  Resource Library (Auto Text)   |  Date Picker   |  Combine Worksheets   |  Encrypt/Decrypt Cells    Send Emails by List   |  Super Filter   |   Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters, ...)   |   50+ Chart Types (Gantt Chart, ...)   |   40+ Practical Formulas (Calculate age based on birthday, ...)   |   19 Insertion Tools (Insert QR Code, Insert Picture from Path, ...)   |   12 Conversion Tools (Numbers to Words, Currency Conversion, ...)   |   7 Merge & Split Tools (Advanced Combine Rows, Split Cells, ...)   |   ... and more

Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time.  Click Here to Get The Feature You Need The Most...

Description


Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier

  • Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
  • Open and create multiple documents in new tabs of the same window, rather than in new windows.
  • Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations