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:

data set with blank rows that needs to be deleted

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
insert the helper column
  1. 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.

formula for the helper column
  1. Select the entire dataset (including the helper column)
  2. Click the Data tab
click the data tab
  1. Click on the Sort icon (in the Sort & Filter group)
click the sort icon in the ribbon
  1. In the Sort dialog box that opens, unchecked the option ‘My data has Headers’
Uncheck my data has headerds option
  1. Open the Sort By drop-down and select Column A (which is our helper column)
select the helper column as the sort by column
  1. Keep the ‘Sort On’ and ‘Order’ values as is
  2. 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.

data set has been sorted with all the blank rows together at the top
  1. Select all the blank rows, right click and delete
select and delete all the blank rows
  1. 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:

data set with blank rows that needs to be deleted

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
insert the helper column
  1. 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.

formula for the helper column
  1. Select the helper column (not the entire dataset).
  2. 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.
  3. In the Find and Replace dialog box, enter ‘Blank’ in the Find what field
enter blank in the Find What field and find and replace dialog box
  1. Check the option – Match entire cell contents
check the match entire cell content box
  1. In the Look in drop-down, select Values.
select values in the look in drop down
  1. Click on the Find all button.
click on the find all button
  1. This will find all the cells that have the value blank in them and show the list below the find and replace dialog box.
all blank cells have been found
  1. 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.
select all the blank cells by using control a
  1. Right-click on any of the selected cells and then click the Delete option.
right click on the selected cells and click on Delete
  1. In the Delete dialog box that opens up, select the Entire row option
select the delete entire row option in the delete dialog box
  1. Click OK. This should remove all the blank rows from your data set
  2. 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:

data set with blank rows that needs to be deleted

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.
Go to dialog box
  1. 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.
click the special button in the go to dialog box
  1. In the Go To Special dialog box that opens up, click on the Blank option
select blanks in the go to special dialog box
  1. 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.

all the blank cells are selected
  1. Right-click on any of the selected blank cells and click on the Delete option
right click on any of the blank cells and click on delete
  1. In the Delete dialog box that opens, select the Entire row option
select the delete entire row option in the delete dialog box
  1. 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.
click on the visual basic icon in the ribbon

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

  1. In the VB editor, click on the Insert option in the menu.
click on the insert option in the menu
  1. Click on the module option. This will insert a new module and open the module code window.
insert a new module
  1. Copy and paste the VBA code I’ve given above in the module code window.
copy and paste the VBA macro code in the module code window
  1. Place your cursor anywhere within the code and click on the Run Sub/Userform icon in the toolbar (or press the F5 key)
run the macro by clicking on the run subroutine icon in the toolbar
  1. 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.

data set with blank rows that needs to be deleted

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
click on the insert table icon in the ribbon
  1. In the Create Table dialog box, make sure that the range is correct and the ‘My Table has headers’ option is checked.
check the range in the create table dialog box
  1. Click Ok. Doing this would convert our dataset into an Excel Table that we can use in Power Query.
  2. Select any cell in the Excel Table
  3. Click the Data tab
  4. In the Get & Transform Data group, click on the From Table/Range option. This will open the Power Query Editor.
click on from table or range option in the data tab
  1. In the Table that shows in the Power Query Editor, click on the first column filter icon.
click on the column filter icon in the first column
  1. Click on Remove Empty. This will remove all the blank cells from the data set.
select the remove empty option
  1. Click on the Close & Load option in the ribbon.
click on the close and load option in the ribbon

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

new worksheet is inserted where blank rows have been deleted

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:

Excel Ebook Subscribe

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

Sumit Bansal
Hello there! I'm Sumit Bansal, founder of trumpexcel.com and an Excel MVP. I started this website in 2013 with a simple goal: to share my love for Excel through easy to follow tips, tutorials and videos. I'm here to help you get the best out of MS Excel to save time and boost your productivity.

8 thoughts on “Delete Blank Rows in Excel (5 Easy Ways)”

  1. Hi Trump excel after moving from sheet 1 sheet 2 meeting upon a condition and delete the blank row from sheet 1 is there any consolidated vba for this please?

    Reply
  2. This sub worked for me WHEN there were blank rows created by a filter I’m using. What test do I need in the event there are NO blank rows? Sometimes that is happening and then I get an error on the routine (no cells were found).

    Reply
  3. hi,
    I have created below macro code to delete first column and blank rows in sheets

    It’s working fine.

    But I want to run this macro for first 17 sheets out of 36 sheets

    Please guide

    Macro code

    Sub Removecolumnblankrows()
    Columns(1).EntireColumn.Delete
    With Range(“A6:A1000”)
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
    End Sub

    Reply
  4. hi,
    I have created below macro code to delete first column and blank rows in sheets

    It’s working fine.

    But I want to run this macro for first 17 sheets out of 36 sheets

    Please guide

    Macro code

    Sub Removecolumnblankrows()
    Columns(1).EntireColumn.Delete
    With Range(“A6:A1000”)
    .SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End With
    End Sub

    Reply
  5. I do have different solutions. can u please provide me your contact details so I can post you the solution? – Kamal

    Reply

Leave a Comment