ExcelInsider Team
Unused empty rows at the bottom of your Excel sheet can interrupt critical activities like data processing, formula application, pivot table formatting, etc. Unfortunately, Excel doesn’t allow you to truly delete all the rows (a total of 1,048,576 rows) up to the last cell of your worksheet.
However, you can delete empty rows with visible borders, formatting, etc., cluttering the bottom of your worksheet in just a few clicks. To do so, highlighting all empty rows at once and using the Delete button is the easiest way.
Steps to highlight blank rows at the bottom and delete them:
➤ Click on the row number of the first empty cell to highlight it.
➤ Use the CTRL + SHIFT + ↓ shortcut keys to highlight all the remaining rows from there to the bottom of the sheet.
➤ Go to the Home tab >> Cells group >> Delete. Now, immediately save your worksheet, close it, and reopen it.
In this article, we’ll cover all the ways of deleting empty rows at the bottom of your Excel worksheet using manual methods, the Go To Specials feature, the IF and COUNTA functions, and VBA coding.
Manually Select and Delete Empty Rows
In our sample dataset, we have 10 rows and 4 columns with customer feedback info. Rows 11 to 17 at the bottom of the screen are empty, yet they contain cell formatting.
Our goal is to remove these empty rows that don’t contain any values. You can select as many bottom rows as you want and use the same methods to delete them.
If you only have a few empty rows at the bottom, you can highlight each row manually and delete them at once. Here’s how:
➤ Identify the first empty cell of your dataset and click on its row number to highlight it.
➤ Now, press the Ctrl key and hold it as you click on each empty row to highlight them.
➤ Once all the blank rows are selected, go to the Home tab and click the Delete button from the Cells group.
➤ Excel will now delete the selected rows.
Manually Highlight Bottom Rows in Bulk and Delete
For a huge number of blank rows, you can use this method to select thousands of empty rows at once (up to row 1,048,576). Once you highlight the rows, you can delete them altogether. Below are the steps:
➤ Manually highlight the first empty row of your sheet. Or, press CTRL + END to highlight the last used cell of your dataset and click on the row number of the next row.
➤ As the first empty row is highlighted, press CTRL + SHIFT + ↓ to select all the remaining sheet rows.
➤ From the Home tab, go to the Cells group and click the Delete button.
➤ Now, immediately save the file and close it. Reopen and the final result will look like this:
Use the Go To Special Feature to Delete Blank Rows at the Bottom
Before you apply this method, make sure there are no blank cells in the previously used rows. Otherwise, Excel will delete any row with a single blank cell even if the other cells in the same row are filled. For a dataset with no blank cells in the used rows, follow these steps to delete blank rows at the bottom:
➤ Go to the Home tab and choose the Find & Select option in the Editing group.
➤ From the drop-down menu, click on Go To Special.
➤ Or, you can press CTRL + G and click on Special.
➤ In the Go To Special box, choose Blanks from the Select group and click Ok.
➤ As Excel highlights the empty cells from your selected range, open the Home tab >> Cells >> Delete.
➤ Now, all the remaining blank rows are deleted.
Applying the IF and COUNTA Functions to Clear Blank Rows
If your dataset is a mix of blank and non-blank cells, we can apply a formula to separate completely empty rows. In Excel, the COUNTA function counts the number of cells in a range that are not empty. We use the IF function to return different values based on whether a condition is true or false. Here’s how to combine them to mark blank rows and remove them:
➤ Create a helper column and enter the following formula in its first cell:
=IF(COUNTA(A2:D2)=0, "Blank", "Data")
➤ Here, A2 is the first used cell of our first row and D2 is the last one. Change the references according to your dataset.
➤ Press Enter and drag the formula down to the last bottom cell that you want to delete. This formula returns the text Blank for empty rows and Data for used rows.
➤ Now, to filter out the blank rows, click on the header row and go to the Data tab. Click on Filter.
➤ As Excel adds a filter drop-down on each column heading, click on the drop-down in the helper column.
➤ From the menu, check the Blank box to filter all blank rows. Press Ok.
➤ Now, all the fully blank rows are highlighted. Select all the filtered rows without the headings. Click on the first cell of the filtered data and use the CTRL + SHIFT + END shortcut to select all the rows up to the last filtered cell.
➤ Go to the Home tab >> Cells >> Delete. All the empty rows are now deleted.
➤ To remove the filter, click on the Data tab >> Filter.
➤ Here’s our final result:
Remove Blank Rows at the Bottom Using a Custom VBA Macro
To automate the removal process, we can code a VBA macro that removes all the remaining empty cells at the bottom of your worksheet. Below are the steps:
➤ To add the Developer tab on your main ribbon, click on the File tab >> More >> Options.
➤ Choose Customize Ribbon and check the Developer box. Press Ok.
➤ Click on the Developer tab and choose Visual Basic from the Code group.
➤ In the VBA Editor, press the Insert tab and select Module.
➤ As the Module box arrives, paste the following code in it:
Sub RemoveEmptyRows()
'-- Declare variables
Dim lastRow As Long ' To store the number of the last row with data
Dim i As Long ' Loop counter for rows
Dim targetSheet As Worksheet ' To hold a reference to the active sheet
'-- Set the target worksheet to the one currently active
Set targetSheet = ActiveSheet
'-- Turn off screen updating to make the macro run faster and prevent screen flicker
Application.ScreenUpdating = False
'-- Find the last row in the used range of the sheet.
' This ensures we only check the part of the sheet that actually contains data.
lastRow = targetSheet.UsedRange.Rows.Count + targetSheet.UsedRange.Row - 1
'-- Loop through all rows from the last row up to the first row.
' It is CRITICAL to loop backwards (from bottom to top) when deleting rows.
' If you loop forwards, deleting a row will shift the rows below it up,
' causing the loop to skip the very next row.
For i = lastRow To 1 Step -1
'-- Check if the entire row is empty.
' WorksheetFunction.CountA counts the number of cells that are not empty in a given range.
' If CountA returns 0, the row is completely blank.
If Application.WorksheetFunction.CountA(targetSheet.Rows(i)) = 0 Then
'-- If the row is empty, delete it.
targetSheet.Rows(i).Delete
End If
Next i
'-- Turn screen updating back on
Application.ScreenUpdating = True
'-- Optional: Display a message box to confirm the process is complete.
MsgBox "Empty rows have been removed.", vbInformation, "Process Complete"
End Sub➤ Press F5 or click on the Run tab >> Run Sub/UserForm.
➤ Now, all the blank rows are removed from your sheet.
Frequently Asked Questions
How to hide blank rows at the bottom in Excel?
Although you can’t really delete the bottom rows, you can hide them for a cleaner look using Excel’s Hide feature. Click on the row number of the first empty row to highlight it. Press CTRL + SHIFT + ↓ to select all the rows at the bottom. Right-click on your selection and choose Hide.
How do you delete thousands of blank columns in Excel?
Press CTRL + END to go to the last used cell in the sheet. Click on the column letter of the first empty column after your data to highlight it. Press CTRL + SHIFT + ↓ to highlight all the remaining cells from there to column XFD (end of Excel). Go to the Home tab >> Cells >> Delete >> Delete Sheet Columns.
How do I remove blank fields in a row?
Select your data range and press CTRL + G . Click on Special and select the Blanks option. Press Ok to highlight all the blank cells of the row. Right-click on any highlighted cells and select Delete from the menu.
Concluding Words
Whether you have a few or a bulk of unused cells, our methods will help you delete them quickly in just a few clicks. Before you select the rows to delete, make sure all the cells are truly blank without any spaces or hidden formulas. If Excel doesn’t recognize the last used cell in your dataset, there might be uncleared data in those cells. Make sure you delete them to use the methods correctly.
