How to Delete Empty Rows at the Bottom in Excel?

Get FREE Advanced Excel Exercises with Solutions!

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.

Delete Empty Rows at 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.

  • Now, right-click on the mouse or press CTRL+ –. As a result, a dialogue box will pop up.

Delete Empty Rows at Bottom manually

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

Delete Empty Rows at Bottom by hiding

  • Now, press CTRL+SHIFT+ Down Arrow + Right Arrow Key. This will select all the cells at the bottom.

Delete Empty Rows at Bottom hide

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

Delete Empty Rows at Bottom by sort

Now, you can see, all the data are sorted leaving the empty cells at the bottom.

Delete Empty Rows at Bottom sorting

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

Delete Empty Rows at Bottom go to special

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.

Delete Empty Rows at Bottom by filter

  • Now, click on the drop-down button and select blanks. See the screenshot below.

Delete Empty Rows at Bottom by filter Data Command

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

Delete Empty Rows at Bottom using Find

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

Delete Empty Rows at Bottom by find option

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.

Delete Empty Rows at Bottom vba

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

Delete Empty Rows at Bottom using VBA

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.


Related Articles


<< Go Back to Delete Multiple Rows Based on Condition | Delete Rows | Rows in Excel | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Mahbubur Rahman
Mahbubur Rahman

MAHBUBUR RAHMAN is a leather engineer with a talent for Excel and a passion for VBA programming. To him, programming is like a time-saving wizard, making data manipulation, file handling, and internet interactions a breeze. His skill set extends to SPSS, STATA, Deep Neural Networks, and Machine Learning. Holding a B.Sc in Leather Engineering from Khulna University of Engineering & Technology, he's shifted to become a content developer. In this role, he crafts technical content centred around Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo