Sometimes, while using Excel we have to deal with different sizes of datasets which may contain blank cells at the bottom or in between rows. Here, we will show you different techniques in Excel to Delete Empty Rows at the Bottom. We will get help from a sample Data set that contains empty rows at the bottom.
As you can see, our data set contains empty rows at the bottom, we will see 7 different methods including VBA to get rid of these empty rows.
1. Applying a Manual Approach to Delete Empty Rows in Excel at Bottom
If we have a small dataset then empty rows are easy to identify, and we can do the job manually.
Steps:
- Press CTRL and select the entire blank rows.
- Select Entire Row and click OK.
You can see, all the blank rows are gone.
2. Deleting Excel Empty Rows at Bottom by Hiding
If we want to reduce our file size and keep our dashboard cleaner we can delete or hide all the unused blank rows at the bottom.
Steps:
- First, select a cell at the bottom of your dataset.
- Now, press CTRL+SHIFT+ Down Arrow + Right Arrow Key. This will select all the cells at the bottom.
- At this point, press CTRL+9 and it will hide all the rows at the bottom.
That’s it. Simple.
3. Using Excel Sort Command to Delete Empty Rows at Bottom
Sorting is one of the easiest methods to delete empty rows at the bottom in Excel. Let’s see, how to do it.
Steps:
- First, select the entire data range you want to sort, go to the Data tab, and select either the Ascending or Descending sorting option as you please.
Now, you can see, all the data are sorted leaving the empty cells at the bottom.
- At this point, we can simply right-click on the mouse or press CTRL+- to delete entire rows at the bottom as we have already shown you in Method 1.
Read More: How to Delete Blank Rows in Excel?
4. Deleting Empty Rows in Excel Using Go to Special Tool
Go to Special is another cool option to delete empty rows.
Steps:
- First, select the entire data range and press CTRL+G and a dialogue box will pop up.
- From the dialogue box, we will select Special and then select Blanks option and click OK as shown in the image below.
After that, our data set will look kind of familiar.
Yes, if you followed Method 1, you know how to do the best work.
Read More: How to Delete Row If Cell Is Blank in Excel?
5. Applying the Filter Tool to Delete Empty Rows at Bottom
We can also use the Filter option when we need to delete empty rows.
Steps:
- First, select the entire data range with headers, then go to the Data tab and select Filter as shown in the image.
- Now, click on the drop-down button and select blanks. See the screenshot below.
- After clicking OK, our data table will look like the following image.
Now, you can select the blank rows and delete them using a right-click on the mouse or shortcut key, as we have mentioned earlier in this article in Method 1.
Related Content: How to Delete Rows in Excel with Specific Text
6. Deleting Empty Rows by Excel Find Tool
While deleting empty rows at the bottom Excel Find option is another easy and effective method.
Steps:
- Select the entire data range and press CTRL+F. A dialogue box will pop up and select Options as shown in the screenshot below.
- After that, Leave the Find What empty, select Look in as Values, and tick on Match entire cell contents. Finally, click Find All.
- As a result, all the blank cells will be visible with their location. From here, we will Press CTRL+A and Close the dialogue box.
So, we will get the result as follows.
Now, you know what to do. If not, follow Method 1.
Related Content: How to Delete Row If Cell Contains Specific Values in Excel?
7. Using VBA Macro to Delete Empty Rows at Bottom
We are going to share with you the code through which, you can easily delete empty rows in Excel.
Steps:
- First, right-click on the sheet and go to View Code.
- After that, copy and paste the VBA code below.
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
- After that, press the F5 or play button to run the code.
Through this code, we are telling Excel to delete all the empty rows in the range B4:Z100. If we have a long dataset we can choose the range accordingly.
Download Practice Workbook
Conclusion
These are the 7 different methods to Delete Empty Rows at the Bottom in Excel. Based on your preferences, you may choose the best alternative. Please leave them in the comments area if you have any questions or feedback.
