Delete Blank Rows in Excel (5 Easy Ways)

While working with large datasets in Excel, you may need to clean the data to use it further.

One common data cleaning step is to delete blank rows from your data in Excel.

In this tutorial, I will show you how to remove blank rows in Excel using different methods.

While there is no in-built feature in Excel to do this, it can quickly be done using simple formula techniques or using features such as Power Query or Go-To Special.

And for VBA aficionados, I’ll also give you a simple VBA code that you can use to quickly remove all blank rows from your data set in Excel.

Delete Blank Rows Using the SORT Functionality

One of the easiest ways to quickly remove blank rows is by sorting your data set so that all the blank rows are stacked together.

Once all the empty rows are together, you can manually select and delete them in one go.

However, you cannot simply apply the sorting on your existing dataset (as it can alter your data set by rearranging the rows while sorting). We will need to add a helper column which we will use to sort the data and then delete the blank rows.

Let me show you how it works using a simple example.

Below I have a data set where I have some blank rows that I want to remove from this data set:

Here are the steps to remove the blank columns using a helper column and sort functionality:

1. We first need to insert a helper column to the left of the data set. To do this, select the column header of the first column, right click and then click on insert

2. Now enter the below formula in cell A1, and then copy this for all the cells in the column

=IF(COUNTA(B1:XFD1)=0,"Blank","Not Blank")

This above formula would give us the result “Blank” when the row is empty and the result “Not Blank” when the row is not empty.

3. Select the entire dataset (including the helper column)
4. Click the Data tab

5. Click on the Sort icon (in the Sort & Filter group)

6. In the Sort dialog box that opens, unchecked the option ‘My data has Headers’

7. Open the Sort By drop-down and select Column A (which is our helper column)

8. Keep the ‘Sort On’ and ‘Order’ values as is
9. Click OK

The above steps would sort your data set so that all the blank rows are stacked up together at the top, and the remaining data set is below the blank rows.

10. Select all the blank rows, right click and delete

11. Once done, feel free to remove the helper column

Note: When we sort our data set using the steps above, it will not mess with the original order of the rows. It will only bring all the blank rows at the top while keeping your original data set intact.

For this method to work, every cell in the blank row needs to be actually blank. If it has a space character or null string, it would not be considered blank.

Also read: How to Delete Rows in Excel (Single or Multiple)?

Delete Blank Rows Using Find and Replace

Another smart way to quickly delete blank rows from your data set is by using the Find and Replace functionality.

Let me show you how it works with an example.

Below have a data set where I have some blank crows that I want to delete:

Here are the steps to do this using a helper column with Find and Replace functionality:

1. The first step is to add a helper column to our dataset. To do this, select the column header of the first column, right-click, and then click on insert. This will insert a blank column to the left of the data set

2. Now enter the below formula in cell A1, and then copy this for all the cells in the column

=IF(COUNTA(B1:XFD1)=0,"Blank","Not Blank")

This above formula would give us the result “Blank” when the row is empty and the result “Not Blank” when the row is not empty.

3. Select the helper column (not the entire dataset).
4. Hold the Control key and press the F key. This will open the Find and Replace dialog box. You can also do this by clicking the Home tab, clicking on the Find & Replace icon in the Editing group, and then clicking on the Find option.
5. In the Find and Replace dialog box, enter ‘Blank’ in the Find what field

6. Check the option – Match entire cell contents

7. In the Look in drop-down, select Values.

8. Click on the Find all button.

9. This will find all the cells that have the value blank in them and show the list below the find and replace dialog box.

10. Hold the Control key and Press the A key once. This will select all the cells that have been found by the Find and Replace option.

11. Right-click on any of the selected cells and then click the Delete option.

12. In the Delete dialog box that opens up, select the Entire row option

13. Click OK. This should remove all the blank rows from your data set
14. Once done, feel free to remove the helper column.

For this method to work, every cell in the blank row needs to be actually blank. If it has a space character or null string, it would not be considered blank.

Also read: Delete Blank Columns in Excel (3 Easy Ways + VBA)

Delete Blank Rows Using Go To Special (Use with Caution)

Let me also show you how to remove blank rows in Excel by using the Go-to special technique.

However, let me warn you that there is a possibility that this may end up deleting some of the rows that may not be completely blank (and may only have a few cells that are blank).

I recommend you do not use this method with large data sets.

Below I have a data set where I have some blank rows that I want to remove:

Here are the steps to do this using the Go To Special technique:

1. Select the entire data set
2. Press F5 on your keyboard to open the Go To dialog box.

3. Click on the Special button. This will open the Go To Special Dialog box. Alternatively, you can click the Home tab, then click the Find & Replace icon in the Editing group, and then click the ‘Go To Special’ option.

4. In the Go To Special dialog box that opens up, click on the Blank option

5. Click OK.

The above steps would select all the cells that are blank in the data set. Since all the cells in a blank row would be empty, this would end up selecting all the blank rows.

6. Right-click on any of the selected blank cells and click on the Delete option

7. In the Delete dialog box that opens, select the Entire row option

8. Click OK

The above steps would remove all the rows that contain blank cells.

CAUTION: This method should only be used if you are sure that there are no blank cells in your data set except the ones that are in the blank rows. In case there are blank cells in an otherwise non-blank row, even these rows would be deleted, as this method works by selecting the blank cells and then deleting the entire row of that blank cell.

Remove Blank Rows Using VBA Macro

If you need to delete blank rows often, you can also consider using a simple VBA macro code to do this.

Even if you are an absolute beginner with Excel VBA macros, don’t worry. I will show you how to set it up properly so that you can use it again and again.

But let me first give you the VBA code.

Below is the VBA code that will go through your entire data set and delete all the blank rows:

'Code Developed by Sumit Bansal from https://TrumpExcel.com

Sub DeleteBlankRows()

Dim EntireRow As Range
On Error Resume Next
MsgBox Selection.Row.Count

Application.ScreenUpdating = False

        For i = Selection.Rows.Count To 1 Step -1
            Set EntireRow = Selection.Cells(i, 1).EntireRow
            If Application.WorksheetFunction.CountA(EntireRow) = 0 Then
                EntireRow.Delete
            End If
        Next

Application.ScreenUpdating = True

End Sub

The above VBA code uses a simple For Next loop to go through each row in your data set and check whether the row is empty or not using the COUNTA function.

As soon as it finds an empty row, it deletes it and moves to the next one.

Now let me show you the steps on how to set up this VBA code to use it in Excel:

1. Select the dataset that has the blank rows that you want to remove
2. Click the Developer tab in the ribbon. If you do not see the developer tab, click here to find out how to get it.
3. Click on the Visual Basic option. This will open the VB editor, where we are going to add the VBA code that I’ve given above.

Excel Tip: You can also use the keyboard shortcut ALT + F11 to open the VB editor

4. In the VB editor, click on the Insert option in the menu.

5. Click on the module option. This will insert a new module and open the module code window.

6. Copy and paste the VBA code I’ve given above in the module code window.

7. Place your cursor anywhere within the code and click on the Run Sub/Userform icon in the toolbar (or press the F5 key)

8. Close the VB Editor

The above steps would remove all the blank rows from your data set.

Here are a few things you need to know when using this macro in Excel:

• Once you have added the VBA code to your Excel file, you need to save your Excel file as a Macro-enabled file (with a .XLSM extension)
• If you follow the steps above to insert a module and then add this code to the module, it is only going to work in the workbook where it has been added.
• If you want this code to work on all your Excel workbooks, you should save this in your personal macro workbook. Once the code has been saved in the Personal Macro Workbook, you can use it in any Excel workbook on your system.

Note: Remember that any changes done through a VBA code are irreversible, and you will not be able to get your original data back after you have run the macro code. So it’s always a good idea to make a backup copy of your data just in case you need it in the future.

Delete Blank Rows Using Power Query (Get & Transform)

Another really quick way to remove blank rows from a dataset Excel is by using Power Query.

With Power Query, you can open your data set in the Power Query Editor and delete all the blank rows with a few clicks. When you have the desired result in the Power Query Editor, you can quickly get this data back in Excel as a new table.

Let me show you how it works.

Below I have the same data set where I have some blank rows that I want to remove.

Here are the steps to delete blank rows using Power Query:

1. The first step would be to convert your data into an Excel table (if it’s not in the Excel table format already). To do this, select the dataset, then click on the Insert tab in the ribbon, and then click on the Table icon. Or you can use the keyboard shortcut Control + T

2. In the Create Table dialog box, make sure that the range is correct and the ‘My Table has headers’ option is checked.

3. Click Ok. Doing this would convert our dataset into an Excel Table that we can use in Power Query.
4. Select any cell in the Excel Table
5. Click the Data tab
6. In the Get & Transform Data group, click on the From Table/Range option. This will open the Power Query Editor.

7. In the Table that shows in the Power Query Editor, click on the first column filter icon.

8. Click on Remove Empty. This will remove all the blank cells from the data set.

9. Click on the Close & Load option in the ribbon.

The above steps would insert a new worksheet in your workbook where the resulting table would be inserted.

One huge benefit of using Power Query is that when you have set the process once, you can reuse this query again and again.

For example, in case you change your original data set, or add more rows to your data set, then you do not need to repeat the same steps. you can simply go to the resulting table you got after step 9, right-click on any of the cells and then click on Refresh.

When you refresh the query, it repeats the same steps in the back end, where it goes back to the original table, checks the data, removes the blank rows, and updates the resulting table in a few seconds.

In this tutorial, I showed you five different ways to delete blank rows from your data set in Excel.

The easiest would be to use a helper column and then and then either use the sort functionality to stack all the blank rows together and delete them, or use Find and Replace to find all the blank rows and delete them manually.

Another easy and popular way to remove blank rows is by using the Go To Special technique. However, you should use it cautiously as it can also end up deleting those rows that are not completely empty.

If you’re comfortable using VBA, you can also use a simple macro code I have given in this article to quickly all the blank rows.

And finally, I have also covered how to do this using Power Query.

Other Excel articles you may also like:

• How to Delete Alternate Rows in Excel
• Delete Blank Columns in Excel
• The Ultimate Guide to Find and Remove Duplicates in Excel.
• Remove Spaces in Excel – Leading, Trailing, and Double.
• How to Insert Multiple Rows in Excel.
• Insert a Blank Row after Every Row in Excel (or Every Nth Row)
• 7 Quick & Easy Ways to Number Rows in Excel.
• Useful Excel Macro Examples.
• Delete rows based on cell value in Excel