Grab our practice workbook 👉 HERE and follow along.
Blank rows in Excel can be a real headache. They mess up sorting, filtering and formulas, making your data unreliable. These rows often appear when importing data from a database or combining datasets.
Unfortunately, there is no single solution that works all the time. The best way to remove blank rows depends on your data structure.
In this guide, you’ll learn multiple methods to quickly delete empty rows in Excel.
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:
- Select the Row: Click the number of the row you want to delete.
- 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
Ctrlkey while clicking the row numbers to select them individually. - Remove Blank Rows: Right-click the selected row number(s) and choose “Delete” from the context menu.
Featured Course
Excel Essentials for the Real World
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 + Endto 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.
💡 For more tips on how to use Advanced Filter in Excel, check out this article.
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 + Cto copy it. Next, scroll down to the end of your dataset. HoldShiftand click the last cell in the helper column you want to select. Then pressCtrl + Vto 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 + F8to 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.
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
