How to Delete Empty Rows at Bottom in Excel (7 Ways)

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 Delete Empty Rows at Bottom. We will get help from a sample Data set that contains empty rows at the bottom.

Delete Empty Rows at Bottom


Download Practice Workbook


7 Ways to Delete Empty Rows at Bottom in Excel

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.


Method 1: Excel Delete Empty Rows at Bottom Manually

If we have a small dataset then empty rows are easy to identify, 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.

Read More: How to Use VBA to Delete Empty Rows in Excel 


Method 2: Delete 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.

Read More: How to Delete Hidden Rows in Excel (3 Methods)


Method 3: Delete Blank Rows at Bottom Using Sort Command

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 and go to the Data tab and select either 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 mouse or press CTRL+- to delete entire rows at the bottom as we have already shown you in Method 1.

Read More: Delete Blank Rows in Excel (10 Handy Methods)


Similar Readings:


Method 4: Delete Empty Rows in Excel Using Go to Special

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 rest work.

Read More: How to Delete Row Using VBA (14 Ways)


Method 5: Delete Empty Rows at Bottom by Excel Filter

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.

Read More: How to Delete Filtered Rows in Excel (5 Methods)


Method 6: Delete Empty Rows by Excel Find Option

While deleting empty rows at 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 Filter and Delete Rows with VBA in Excel (2 Methods)


Method 7: Delete Empty Rows at Bottom Using VBA

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.

Read More: Excel Shortcut to Delete Rows (With Bonus Techniques)


Practice Section

The single most crucial aspect in becoming accustomed to these quick approaches is practice. As a result, I’ve attached a practice workbook where you may practice these methods.

Delete Empty Rows at Bottom in EXCEL


Conclusion

These are 7 different for Excel Delete Empty Rows at Bottom. 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

Mahbub
We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo