Get the Remove Blank Rows Practice Workbook
Want the file without building it from scratch? Download the ready-to-use workbook and follow along with every method in this tutorial.
- Practice all 5 ways to remove blank rows in Excel
- Test the safer COUNTA method on real data before deleting anything
- Includes the VBA macro example for repetitive cleanup tasks
Enter your email below for instant access.
How to Delete Blank Rows Manually
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.
How to Filter and Delete 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.
How to Remove Blank Rows with Go To Special
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.”
This method also works for selecting blank cells when using COUNTIF to count blanks before deciding whether to delete them.
How to Find Blank Rows with a COUNTA Helper Column
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.
How to Remove Blank Rows with Power Query
If you need to remove blank rows regularly (e.g., from recurring data imports), Power Query is the best option.
It remembers the transformation and reapplies it automatically when data refreshes.
- Select your data and go to Data > From Table/Range
- In the Power Query Editor, select the Home tab
- Click Remove Rows > Remove Blank Rows
- Click Close & Load
Every time you refresh the query (Data > Refresh All), Power Query removes blanks again without manual steps.
Want to learn Power Query from scratch? See our Power Query beginner-to-pro course.
How to Remove Blank Rows with a 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‘
Once your blank rows are removed, consider hiding rows you don’t need instead of deleting them, or protecting cells to prevent accidental data loss.
Download the Free Practice File
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
Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.
