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.
- Press CTRL and select the entire blank rows.
- 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.
- 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.
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.
- 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.
- 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.
- 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 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.
- 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.
Method 6: Delete Empty Rows by Excel Find Option
While deleting empty rows at bottom Excel Find option is another easy and effective method.
- 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 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.
- First, right-click on the sheet and go to View Code.
- After that, copy and paste the VBA code below.
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.
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.
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.