One of the most annoying things in Excel is having blank rows. Especially when working with a large data set with hundreds of rows. Deleting the rows manually is not an option as it will consume a lot of time.
Although Excel has no inbuilt feature, you can use some tricks to get it done in no time. For example, you can use the Find and Select feature, VBA macros, filter and many more.
In this tutorial, I will guide you on how to delete rows in Excel in several ways. You will get to choose the easiest according to your workbook or Excel skills. Lastly, you can use the practice workbook attached to this tutorial to follow along.
If you are looking at how to delete every other row then you need to refer to this other guide. Let’s get started!
Using Find and Select
Find and select is the easiest method you can use to delete blank rows in Excel. This is an Excel function that helps users to find anything located in their workbooks.
Apart from finding and selecting data, you can also use this feature to find and select blank rows. Once you achieve that, then it becomes easy to delete the blank rows.
Follow these simple steps:
1. Navigate to the Home Tab, then click on Find and Select, which is located at the top right corner
2. Now click Go to Special option
3. Choose Blanks
4. Click Ok to apply changes. This action highlights all the blanks rows and cells in your workbook
5. Click on Delete Dropdown, which is still located under the home tab on the Cells group
6. Next, click on Delete Sheet Rows, which is the second option.
This is the option that deletes all the selected cells.
That’s how to delete blank rows in easy steps.
Using Filter
Filter is one of the most used features in Excel. You might have come across it in one way or another. As the name suggests, this feature filters the cells according to certain conditions. You can filter out blank rows and then delete them. Here are the steps:
1. Highlight all the data in your spreadsheet containing the data you wish to clean
In case your file contains large data, you can use the shortcut CTRL + A or CMD+ Shift +Down key on Mac
2. Navigate to the Data Tab on the Menu
3. Now click the filter option and un-select the blanks, then Click Ok
This action deselects all the blank rows. Now copy and paste only the selected data to a new location. Viola, you have removed the empty rows successfully.
4. Alternatively, you select only the blanks in the filter option. This action will hide all the rows containing data.
5. On the left, you will notice some cells highlighted in blue. This shows you the range of the hidden rows that contain data.
You can go ahead to delete these rows. Once you do that, you can repeat the above steps, then deselect blanks, and the data will reappear again on your spreadsheet. But this time, it will be cleaned up without the empty rows in the data.
Using VBA code
You can easily get rid of blank rows from your data using VBA. Here are the steps.
1. Open the spreadsheet containing your data
2. Open the VBA editor through the developer tab or by the shortcut Alt+f11 on Windows and Fn+Opt+F11 on Mac
3. Click on Insert from the top menu, then select module
4. Copy and paste the following VBA code into the editor
Sub DeleteBlankRows()
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Select the range:", Type:=8)
On Error GoTo 0
If rng Is Nothing Then
MsgBox "No range selected. Macro will exit.", vbExclamation
Exit Sub
End If
Dim row As Range
Dim blankCount As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For Each row In rng.Rows
If WorksheetFunction.CountA(row) = 0 Then
row.Delete
blankCount = blankCount + 1
End If
Next row
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox blankCount & " blank row(s) deleted.", vbInformation
End Sub
5. Click on the green run button from the menu or press F5 to run the macro
6. A pop-up will ask you to select the range you want to delete blank rows. Use the mouse to select the range containing data and blank rows.
That’s how to delete blank rows in your data using VBA code.
Delete empty rows using Addin.
Addins make repetitive tasks in Excel easy. They add more functionality and improve existing features. Third parties develop several add-ins. However, in this section, I will focus on two major ones that are easy to use and contain a lot of features.
Ablebits
Here are the steps
1. Download and install the add-in from ablebits.com
2. Click on a cell located within the table
3. Navigate to the Ablebits Tools add-in tab
4. Find the Transform Group, which is located on the top left tab, and click on delete blanks, then Empty rows
5. Confirm that you want to delete blank rows by clicking Ok. All the empty rows will be deleted successfully.
Kutools for Excel
Kutools is one of the most used Excel add-ins. Follow these steps to remove empty rows.
1. Download and install Kutools from Extend office website.
2. Open the spreadsheet containing the data you wish to clean
3. Select the range containing your data. You can use the Mouse.
4. Navigate to the Kutools Tab>Delete Blank Rows>In selected range (The other options are for active sheet, selected sheets or in all sheets)
5. A dialogue box will pop up asking if you want to delete “entire row”
6. Press Ok
Kutools will delete all the blank rows successfully
Delete blank rows using Sort
You can sort all empty rows so that they appear at the top or at the bottom of your data. Here are the steps:
1. First, select the range containing all your data
2. Navigate to the Home tab>Editing group, then select “Sort and Filter.”
3. Now choose Custom Filter from the drop-down menu
4. Choose the column that you want to use for sorting. If you want to include more than one column, just click on “Add Level.”
5. Select “Values” under the sort on the drop-down menu
6. Now choose the order of values, whether its from “Largest to Smallest” Z-A or Smallest to Largest. A- Z
7. Click Ok to apply changes. The rows will be sorted according to your criteria. Empty rows will either be on top or at the bottom of the data depending on the option you choose
8. Now highlight the blank rows and click on delete. All the empty rows will be removed successfully
Using keyboard shortcuts
You can use a keyboard shortcut to get rid of empty rows. Here are the steps
1. Select the blank rows using shift or ctrl and mouse
If they are a few rows, you can select the rows manually.
2. Use the keyboard shortcut ctrl+- (hold down the ctrl button, then press the minus button on your keyboard.
Download Practice Sheet
Delete Blank Rows using Find and Replace plus COUNTA Function.
Find and Replace is another smart method you can use to get rid of blank rows. Follow these easy steps.
1. First, you need to create a helper column as the first column. You can easily do this by right-clicking on the first column and then clicking Insert. This will insert a new blank column at the beginning.
2. Now, enter this formula into the first cell and drag it down to the remaining cells.
=IF(COUNTA(B1:F1)=0,”Empty”,”Not Empty”)
This formula inserts the word empty on every blank row and not empty on every row that contains data.
3. Now select the whole helper column only (do not select the range containing your data)
4. Press and hold the CTRL key on your keyboard, then press the F key (Ctrl+F). Alternatively, you can open the Find and Replace dialogue box by navigating to the Home tab>Find and Replace>Find
5. Type the word Empty in the find what section.
6. Next, tick the option “Match entire cell contents.”
7. Under the lookup option, select values from the drop-down and click Find All
The Find All option finds and displays all the cells with matching criteria which are blank rows, and displays them in the section below.
8. Press Ctrl+A (Hold down the ctrl button, then press the A button). This action selects all the cells listed
9. Now Right-click on one of the highlighted cells and click on delete
10. A dialogue box will pop up. Select the Entire row and click ok
All the blank rows from your data will be deleted successfully.
11. You can now delete the helper column to remain with your clean data
