How to Delete Empty Rows at the Bottom in Excel?

Our dataset contains empty rows, and we’ll remove these empty rows in various ways.

Method 1 – Manual Approach to Delete Empty Rows in Excel

Steps:

• Press Ctrl and select the entire blank rows by clicking the row headers or dragging through the cells.

• Right-click on the selection and choose Delete or press Ctrl + – (minus). A dialog box will pop up.

• Select Entire Row and click OK.

Method 2 – Deleting Excel Empty Rows at the Bottom by Hiding

Steps:

• Select a cell at the bottom of your dataset.

• Press Ctrl + Shift + Down Arrow + Right Arrow. This will select all the cells after it.

• Press Ctrl + 9 and Excel will hide all the rows at the bottom.

Method 3 – Using the Excel Sort Command to Delete Empty Rows at the Bottom

Steps:

• Select the entire data range you want to sort, go to the Data tab, and select either the Ascending or Descending sorting option.

All the data is sorted, leaving the empty rows at the bottom.

• Follow Method 1 to remove the rows.

Read More: How to Delete Blank Rows in Excel?

Method 4 – Deleting Empty Rows in Excel Using the Go to Special Tool

Steps:

• Select the entire data range and press Ctrl + G.

• From the dialog box, select Special.
• Select Blanks and click OK as shown in the image below.

Here’s the result. All empty rows will be selected.

• Remove the rows as in Method 1.

Read More: How to Delete Row If Cell Is Blank in Excel?

Method 5 – Applying the Filter Tool to Delete Empty Rows at the Bottom

Steps:

• Select the entire data range with headers, then go to the Data tab and select Filter as shown in the image.

• Click on the drop-down button and select Blanks.

• After clicking OK, our data table will look like the following image.

• Select the blank rows and delete them as in Method 1.

Related Content: How to Delete Rows in Excel with Specific Text

Method 6 – Deleting Empty Rows with the Find Tool

Steps:

• Select the entire data range and press Ctrl + F.
• A dialog box will pop up. Select Options as shown in the screenshot below.

• Leave the Find What empty, select Look in as Values, and tick Match entire cell contents.
• Click Find All.

• All the blank cells will be visible with their location.
• Press Ctrl + A and close the dialog box.

We will get the result as follows.

• Remove the rows like in Method 1.

Related Content: How to Delete Row If Cell Contains Specific Values in Excel?

Method 7 – Using VBA Macro to Delete Empty Rows

Steps:

• Right-click on the sheet name and go to View Code.

• Copy and paste the VBA code below into the window.

VBA code:

Sub DeleteEmpty()
  Dim Myrange As Range, rows As Long, i As Long
  Set Myrange = ActiveSheet.Range("B4:Z100")
  rows = Myrange.rows.Count
  For i = rows To 1 Step (-1)
    If WorksheetFunction.CountA(Myrange.rows(i)) = 0 Then Myrange.rows(i).Delete
  Next
End Sub

• Press the F5 or the Play button to run the code.

Download the Practice Workbook

Related Articles

• How to Delete All Rows Below a Certain Row in Excel?
• How to Remove Highlighted Rows in Excel?
• How to Delete All Rows Not Containing Certain Text in Excel?
• How to Delete Rows Based on Another List in Excel?

<< Go Back to Delete Rows | Rows in Excel | Learn Excel