Data comes in all shapes, sizes and forms, so it’s not uncommon to find a dataset with a lot of blank rows. Sometimes it’s not an issue; other times, it causes big problems. Therefore, the best practice in Excel, is to remove blank rows. If there are only a few blanks, we could do this manually, but what if there are hundreds or thousands? That’s not fun.
In this post, we will look at the best methods to remove blank rows quickly and accurately.
Download the example file
I recommend you download the example file for this post. Then you’ll be able to work along with examples and see the solution in action, plus the file will be useful for future reference.
Download the file: 0003 Remove blank rows.zip
The manual method – please don’t do this!!!
The first solution we could turn to is the manual option, but please don’t do this unless there are only a few rows.
Look at the Scenario 1 – Simple data tab in the example workbook. The data looks like this:
Let’s remove those blank rows.
- Hover over the row number. The mouse icon will change to an arrow.
- Click the mouse to highlight the entire row.
- Hold down the Ctrl key.
- Repeat steps 1 and 2 until all blank rows have been selected.
- Release the Ctrl key. The worksheet should now look like this:
- Right-click on any of the highlighted cells and select Delete from the menu.
Instead of right-clicking, you could select Home -> Delete -> Delete Sheet Rows.
That didn’t take too long, did it? But then again, we only had to delete five rows.
Now let’s move on to look at some quicker options.
Automatically find blank cells
When working with simple data, like that contained within the Scenario 1 – Simple data tab, we can use a better method.
- Select a column of cells from the data range.
- Click Home -> Find & Select -> Go To Special… (or press Ctrl + G).
- From the Go To Special window, select Blanks and click OK.
- Only the blank cells are highlighted.
- Right-click on one highlighted cell, select Delete from the menu.
- The Delete dialog box will open. Select Entire row, then click OK.
That’s it. The blank rows have been removed with just a few clicks.
Now, try this method on a dataset where the blank rows are not all in complete rows. Use the Scenario 2 – Complex data tab in the example workbook. Hmmm… that won’t work. Look at the screenshot below, the data in the purple bordered cells would be deleted.
For more complex data, we need another solution.
If our data is more like the complex scenario shown above, we could use filtering.
- Select the whole data range.
- Click Data -> Filter to apply the filter.
- Filter the first column to only show (Blanks), then click OK.
- Repeat the filtering for blanks for every column, or until there are only blank rows remaining.
- The data set will look like this. Rows 3, 5, 8, 10, and 14 are completely blank rows.
- Select all the rows, right-click and click Delete from the menu.
- Remove the filters by clicking Data -> Filter.
All that is left are the rows we want to keep.
Still too time-consuming? Then let’s look at a macro solution next.
VBA macro method
If you remove blanks rows a lot, then a macro might be a faster option.
The VBA code below is available in the example workbook.
The macro will only delete completely blank rows. Therefore, it will work with the basic and more complex scenarios. Select the full data range, and run the following macro.
Sub DeleteBlankRows() Dim i As Long Dim rng As Range Set rng = Selection 'Loop backwards through the rows 'ensures the rows delete correctly For i = rng.Rows.Count To 1 Step -1 'Check if the row is entirely blank If WorksheetFunction.CountA(rng.Rows(i)) = 0 Then 'Delete the entire row rng.Rows(i).EntireRow.Delete 'To delete only the selected columns use this instead: 'rng.Rows(i).Delete Shift:=xlUp End If Next i End Sub
Idea: Put this macro is in your Personal Macrobook; it will be available whenever you need it.
Finally, if you’re importing data with blank rows from CSV, Text or other workbooks, it may be easier and faster to use Power Query. To learn how to do this, read my Power Query series.
There are lots of options to remove blank rows in Excel. Yet, you need to be aware of your data, as selecting the wrong method could easily result in deleting rows we want to keep.
About the author
Hey, I’m Mark, and I run Excel Off The Grid.
My parents tell me that at the age of 7 I declared I was going to become a qualified accountant. I was either psychic or had no imagination, as that is exactly what happened. However, it wasn't until I was 35 that my journey really began.
In 2015, I started a new job, for which I was regularly working after 10pm. As a result, I rarely saw my children during the week. So, I started searching for the secrets to automating Excel. I discovered that by building a small number of simple tools, I could combine them together in different ways to automate nearly all my regular tasks. This meant I could work less hours (and I got pay raises!). Today, I teach these techniques to other professionals in our training program so they too can spend less time at work (and more time with their children and doing the things they love).
Do you need help adapting this post to your needs?
I'm guessing the examples in this post don't exactly match your situation. We all use Excel differently, so it's impossible to write a post that will meet everybody's needs. By taking the time to understand the techniques and principles in this post (and elsewhere on this site), you should be able to adapt it to your needs.
But, if you're still struggling you should:
- Read other blogs, or watch YouTube videos on the same topic. You will benefit much more by discovering your own solutions.
- Ask the 'Excel Ninja' in your office. It's amazing what things other people know.
- Ask a question in a forum like Mr Excel, or the Microsoft Answers Community. Remember, the people on these forums are generally giving their time for free. So take care to craft your question, make sure it's clear and concise. List all the things you've tried, and provide screenshots, code segments and example workbooks.
- Use Excel Rescue, who are my consultancy partner. They help by providing solutions to smaller Excel problems.
Don't go yet, there is plenty more to learn on Excel Off The Grid. Check out the latest posts: