Delete Blank Rows in Excel (with and without VBA)

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 it.

Now there are various ways you can use to do this:

  • Go to each blank row and delete it manually (too tedious and time-consuming).
  • Sort the entire data set so that all the blank rows are at the bottom (works but alters the data set).
  • Use Go To special technique (or a macro) to select all the blank cells at once and delete the rows (way to go).

Delete Blank Rows in Excel

In this tutorial, you’ll learn two ways to quickly select the blank cells in a data set:

  • Using the Go To Special dialogue box.
  • Using a VBA Macro.

Once you have the blank cells selected, you can easily delete these.

Using Go To Special Technique to Delete Blank Rows in Excel

Suppose you have a dataset as shown below:

Delete Blank Rows in Excel - Dataset

The above data has three blank rows in it.

Here are the steps to quickly delete blank rows from this dataset.

  • Select the entire dataset (A1:C13 in this case).
  • Press the F5 key. It will open the Go To dialog box.
    • You can get this dialog box from Home –> Editing –> Find and Select –> Go To.
  • In the Go To dialog box, click on the Special button. This will open the Go To Special dialog box.Delete Blank Rows in Excel - F5
  • In the Go To Special dialog box, select Blanks.Delete Blank Rows in Excel - select blanks
  • Click OK.Delete Blank Rows in Excel - OK

This would select all the blank cells in the dataset.

Delete Blank Rows in Excel - Blank rows selected

Now that you have all the blank rows selected, here are the steps to delete these rows:

  • Right-click on any of the selected cells and select Delete.Delete Blank Rows in Excel - delete right click
  • In the Delete dialog box, select ‘Entire row’.Delete Blank Rows in Excel - entire row
  • Click OK.

This would instantly delete all the blank rows in the dataset.

Delete Blank Rows in Excel - deleted rows

Note that this technique would delete the entire row, which means that if you have any data to the left/right of your dataset, it will get deleted too. If you don’t want this to happen, select Shift cells up option. This would only delete the cells/rows in the data set, and all the other cells around it would remain intact.

Here is a video that show how to use the above steps to delete blank rows in Excel.

Using a VBA Macro to Delete Blank Rows in Excel

While the Go To special technique is way faster than any other regular method, if your work requires you to delete blank rows multiple times in the same workbook, using a simple VBA macro could be faster.

You can write a simple VBA code that will select all the blank cells in a dataset. Once selected, you can format these, delete these, or enter a value in it.

While this can be done using Excel Go To Special dialog box, if you create a macro and add it to the Quick Access Toolbar, you will be able to select all the blank cells with a click of a button (saving you some clicks).

Here are the steps to create a VBA code to select blank cells/rows in Excel:

  • With the workbook selected, press ALT + F11. This will open the VB Editor window.
    Delete Blank Rows in Excel - VBA
  • In the VB Editor, there would be project explorer pane on the left. If you can’t find it, Go to View –> Project Explorer.Delete Blank Rows in Excel - project explorer
  • Right-click on any of the objects for the workbook in which you’re working and go to Insert –> Module.Delete Blank Rows in Excel - insert module
  • In the module window, copy paste the following code:
    Sub SelectBlanks()
    Selection.SpecialCells(xlCellTypeBlanks).Select
    End Sub

    Delete Blank Rows in Excel - code in module

  • Close the VB Editor window or press Alt + F11 to return to the worksheet.
  • Right-click on any of the tabs in the ribbon and select Customize Quick Access Toolbar.Delete Blank Rows in Excel - customize QAT
  • In the Excel Options dialog box, under the choose commands from the drop-down, select Macros.
  • Select the Macro SelectBlanks and click on the Add button.Delete Blank Rows in Excel - macro
  • Click OK.

Now you’ll have the macro available in the QAT. This is just a one time process and now whenever you have a dataset and you want to delete blank rows from it, select the data and click on the Macro icon from the QAT.

This would select all the blank rows and you then delete it.

Note:

  • Since this workbook contains VBA code, make sure you save it in the .xls or .xlsm format.
  • Using Macro from the QAT would work ONLY for the workbook in which you have the macro code. If you want this to work in another workbook, you’ll have to put the code in it (the Macro button in the QAT will be available in all the workbooks though). If you want this to be available in all the workbooks, consider creating an Add-in.

Caution: You can also delete the blank rows using the macro, but since anything done by the macro can’t be undone, it’s a good practice to delete the rows manually.

You May Also Like the Following Excel Tutorials:

  • Rima Palav says:

    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

  • Rima Palav says:

    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

  • Kamal Bharakhda says:

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

  • Chris says:

    How’d you get the tops of your dialog boxes green?!

    • Sumit Bansal says:

      Hey Chris.. I started using MS O365 Proplus.. I guess it has the green bar at the top in dialog boxes.

      • jon says:

        i think, how to hide and show blank row

  • >