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.

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.

• Select Entire Row and click OK.

You can see, all the blank rows are gone.

Read More: How to Delete Rows in Excel: 7 Methods

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.

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

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.

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

Read More: Delete All Rows Below a Certain Row in Excel (6 Easy Ways)

Similar Readings

• How to Delete Selected Rows with Excel VBA (A Step-by-Step Guideline)
• Delete Rows Based on Another List in Excel (5 Methods)
• How to Delete Rows in Excel without Affecting Formulas (2 Quick Ways)
• Delete Rows in Excel with Specific Text (3 Methods)
• How to Delete Row Using Macro If Cell Contains 0 in Excel (4 Methods)

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.

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 Rows in Excel That Go on Forever (5 Easy 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.

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

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.

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

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

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.

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

Read More: Excel Delete Rows in a Range with VBA (3 Easy Ways)

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.

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

• How to Filter and Delete Rows with VBA in Excel (2 Methods)
• How to Delete All Rows Not Containing Certain Text in Excel
• Delete an Excel Row If a Cell Contains Specific Values (3 Methods)
• Excel VBA Code to Delete Rows Based on Multiple Cell Value (3 Criteria)
• Macro to Delete Row in Excel If Cell is Blank
• How to Delete Alternate Rows in Excel (5 Quick Ways)