How to Delete Blank Rows in Excel Easily and Quickly

Using Filters to Delete Blank Rows in Excel

This method is suitable for deleting empty rows that are between data that have header or title columns. It also allows you to filter data based on certain criteria before deleting its blank rows. Here are the steps:

  1. Select all existing data in your Excel worksheet by pressing the shortcut “Ctrl + A” on the keyboard or by clicking the Select All button located in the upper left corner of the Excel window.
  2. Click the “Data” tab on the ribbon, then click “Filter” in the “Sort & Filter” group. Excel will add a small arrow in each column header or heading.
  3. Click the small arrow in one of the header or title columns, then uncheck the “Select All” option. Check only the “Blanks” option, and then click OK. Excel displays only the empty rows that are in that column.
  4. Select all blank rows displayed by pressing the shortcut “Ctrl + A” on the keyboard or by clicking the “Select All” key located on the Excel ribbon.
  5. Right-click on one of the selected blank rows, and then select the “Delete” option on the menu that appears. In the “Delete” dialog box, select the “Entire row” dialog box, and then click OK. Excel deletes all empty rows in that column.
  6. Repeat steps 3 through 5 for any header columns or other headings that have blank rows between their data.
Other Interesting Articles

Using Macros to Delete Blank Rows in Excel

This method is suitable for deleting a large number of empty rows or if you often do this task and want to shorten your time and effort. This method requires you to create and run a macro or VBA code that can delete blank rows automatically. Here are the steps:

  1. Open the Excel worksheet that you want to delete blank rows from, and then press the shortcut “Alt + F11” on your keyboard to open the “Visual Basic Editor” window.
  2. Click the “Insert” menu, then select the “Module” option. Excel will add a new module under “Project Explorer”.
  3. Type the following code in the module window:
Sub DeleteBlankRows()
Dim ws As Worksheet
Dim rng As Range
Dim lastRow As Long
Dim i As Long

Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
Set rng = ws.Range("A1:A" & lastRow)

Application.ScreenUpdating = False

For i = lastRow To 1 Step -1 
    If WorksheetFunction.CountA(rng.Rows(i)) = 0 Then 
        rng.Rows(i).EntireRow.Delete 
    End If
Next i

Application.ScreenUpdating = True 

MsgBox "Blank rows have been deleted."

End Sub
  1. End SubClick the Run menu, then select the Run Sub/UserForm option or press the F5 shortcut on your keyboard to run the macro. Excel will delete all existing blank rows in your worksheet and display a confirmation message.

Conclusion

In this article, we have discussed four ways to delete blank rows in Excel easily and quickly, namely:

  • Delete blank rows manually
  • Use Go To Special
  • Use filters
  • Using macros

You can choose the way that best suits your needs and preferences. Hope this article was useful and good luck!

Latest Articles

Related Articles