Grab our practice workbook 👉 HERE and follow along:

Blank rows in Excel can cause many problems. They disrupt sorting and filtering and lead to errors in formulas. These rows often appear when exporting data from a database or combining datasets.

Unfortunately, there is no single solution that works all the time. The best method depends on your specific data. In this blog post, we will show you how to delete empty rows in Excel using different methods.

This will keep your spreadsheets clean and functional. Read on to discover the best ways on how to remove empty rows in Excel efficiently.

Method 1: Remove Individual Blank Rows

When to Use:

This method is quick and effective for small datasets or when you only have a few blank rows to remove.

Here’s How:

  1. Select the Row: Click the number of the row you want to delete.
  2. Select Multiple Rows: If there are multiple blank rows next to each other, click and drag to select them all. If the blank rows are not next to each other, press and hold the Ctrl key while clicking the row numbers to select them individually.
  3. Remove Blank Rows: Right-click the selected row number(s) and choose “Delete” from the context menu.

Method 2: Filter and Remove Blank Rows

When to Use:

This method is great for large datasets with scattered blank rows.

❗Make sure that the blanks are always in the same column and ideally the entire row is blank. You must reset the filter and repeat the steps if the dataset changes.

Here’s How:

  • Select All Data: Click the top-left corner of your sheet to select all cells.
  • Apply Filter: Go to the “Data” tab and click “Filter.”
  • Filter Blank Rows: Click the filter arrow, uncheck “Select All,”. Then scroll down to the end of the list, and check the ‘Blanks’ option. Click on ‘OK’.
  • Select Blank Rows: Click the first blank row’s number, then press Ctrl + Shift + End to select the visible blank rows.
  • Delete Blank Rows: After selecting the blank rows, right-click on any of the selected row numbers and choose “Delete Row“.
  • Click OK in the “Delete entire sheet row?” dialog box.
  • Clear the Applied Filter: Go to the “Data” tab and press the “Clear” button to remove the filter.

Method 3: Find & Select

When to Use:

This method is great for large datasets with scattered blank rows. But only use it when you’re sure that always the entire row is blank.

❗ Do not use this method if only some cells are blank, as shown in this example:

You might accidentally delete the wrong rows. For such datasets, use Method 4 or Method 5 below.

Here’s How:

  • Open Find & Select: Go to the “Home” tab, click “Find & Select,” and choose “Go To Special…”
  • Select Blanks: Choose “Blanks” and click “OK.”
  • Remove Blank Rows: Go to the “Home” tab, click “Delete,” and choose “Delete Sheet Rows.”

Method 4: Use the COUNTA Function

When to Use:

Use this method when you have a large dataset and need to ensure that no data is mistakenly removed. The COUNTA formula helps identify rows that are completely empty, making it a precise way to handle large amounts of data.

Here’s How:

  • Add a Helper Column: Add a new column at the end of your data.
  • Enter the Formula: In the first row of the new column, type ‘=COUNTA(‘ and then highlight the cells in that row you want to count. Finish the formula with a ‘)‘. For example, for our data the formula looks like this:
=COUNTA(A2:D2)
  • Copy the Formula Down: Click the cell with your formula (e.g., E2). Press Ctrl + C to copy it. Next, scroll down to the end of your dataset. Hold Shift and click the last cell in the helper column you want to select. Then press Ctrl + V to paste the formula across the full range of cells.
  • Select All Data: Click the top-left corner of your sheet to select all cells. Then go to the “Data” tab and click “Filter.”
  • Filter Zero Values: Apply a filter to the helper column. Click the filter arrow, uncheck ‘Select All’, and then check the option to filter rows with a value of ‘0‘.
  • Delete Empty Rows: Select the rows with a value of 0, right-click, and choose “Delete Row.”
  • Clear the Applied Filter: Go to the “Data” tab and press the “Clear” button to remove the filter.

Method 5: Excel VBA Macro

When to Use:

Use this method for large datasets or repetitive tasks. This method automates the process, saving time and reducing errors.

Here’s How:

The macro deletes a line only if the entire row is empty. It uses the COUNTA function, which counts the number of non-empty cells in each row. If the count is zero, meaning the row is completely blank, the macro deletes that row. This loop runs from the bottom of the used range to the top to avoid issues with shifting rows.

  • Open VBA Editor: Press Alt + F11.
  • Insert a New Module: In the VBA editor, go to the menu and select Insert > Module. This will create a new module where you can write your code.
  • Add the VBA Code: Copy and paste the following code into the module window:
Sub RemoveBlankRows_XelPlus()
    Dim Rng As Range
    Dim i As Long
    Set Rng = ActiveSheet.UsedRange
    For i = Rng.Rows.Count To 1 Step -1
        If Application.WorksheetFunction.CountA(Rng.Rows(i)) = 0 Then
            Rng.Rows(i).EntireRow.Delete
        End If
    Next i
End Sub
  • Close the VBA Editor: After pasting the code, close the VBA editor by clicking the X in the top right corner or pressing Alt + Q.
  • Run the Macro: Back in your Excel workbook, press Alt + F8 to open the Macro dialog box. Select ‘RemoveBlankRows_XelPlus’ from the list of macros and click ‘Run

Cleaning up your Excel sheets can make your data analysis smoother and more accurate. Choose the method that best suits your needs and keep your spreadsheets organized!

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master to delete empty rows in Excel with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.