5 Easy Methods To Get Rid of Blank Rows in Excel

5 Easy Methods to Remove Blank Rows in Excel

Blank rows are not a good sign in Excel and disturb at time of calculation and reporting. In this tutorial, we will learn about how to “remove blank rows” in Excel. We will learn here “5”  ways to delete blank rows in Excel step by step.

                 1 (A): COUNTBLANK Formula

                    (B): COUNTA Formula

                    (C): ISBLANK Formula

                    (D): SUM (LEN)Formula

                2: Excel FIND Option

                3: Go To Special Option

                4: Excel Filter Option

                5: VBA Macro Option

Method 1 (A): Using COUNTBLANK Formula

COUNTBLANK function counts blank cells. First, we will have a dataset where we will apply this function.  Below is the image of the data set where we will apply the COUNTBLANK function to remove blank rows.

Remove_Blank_Rows

First of all, we will convert this data into a “Table” format by pressing “CTRL +T” and press “OK” as mentioned in the below image:-

Create_Table

After creating “Table” add a helper column as “Blank” after the last column as shown in the below image:-

Add_Helper_Column

Apply COUNTBLANK function in the first row of the helper column and press Enter and see formula will apply in all rows with the result. See image below:-

Remove_Blank_Rows

Click on “Blank” column drop-down arrow, uncheck the (Select All) box and see the most occurrence of blank cells, in this example “5”. See image below:-

Remove Blank Rows

Press “OK” and we will get all blank rows, See below image:-

Remove Blank Rows

Now select all the blank rows and go to “Home” Tab –>”Delete” Cells tab–>”Delete Sheet Rows” as shown in image below:-

Remove Blank Rows

Remove the filter and see that all blank rows removed.

(B): COUNTA Formula to Remove Blank Rows

In this method, apply “COUNTA” formula in the helper column and press Enter and see the result. See image below:-

Remove Blank Rows

 

“COUNTA” formula counts all character or word in a cell as “1”. If any cell has no data, counts will be “0” for that cell. As we can see in the above image.

Again apply the filter option and this time select “0” count and press “OK”. All the rows will be selected whose counts are “0” and go to “Home” –>”Delete”–>”Delete Sheet Rows”.

Remove Blank Rows

After deleting the blank rows, remove the filter.

(C): ISBLANK Formula to Remove Blank Rows

ISBLANK function is another way to find blank rows also. Apply this formula in helper column. See below image:-

This is an array function press (“Ctrl+Shift+Enter“) instead of simple Enter. Now apply filter for “TRUE” and press “OK” as shown in image below:-

Remove Blank Rows

Select blank rows, Go to “Home” tab–>Cells tab “Delete”–>”Delete Sheet Rows” and press “OK” to remove blank rows. Remove the filter, see blank rows have removed.

(D): SUM(LEN)Formula to Remove Blank Rows

Use SUM with LEN function (array function)  and this function sums all the characters in a cell. See below image:-

Remove Blank Rows

SUM(LEN) function counts all the characters in a cell and sums of it. If there are no characters in any cell, the result would be “0” as we can see in the above image.
 
Now apply the filter to the “Blank” column and find the result of “0”. Select all the row containing “0” result and apply the procedure to delete those blank rows. Go to “Home” tab –> “Delete“–>”Delete Sheet Rows“. Remove the filter and blank rows removed.

2: By Excel FIND Option (Ctrl+F)

Find (Ctrl+F) is another way to find blank rows in Excel. Clicking on “Options” button appearing right side of the image, we get the more option. As we can see image “2”.

Find_Image

Select dataset and press “Ctrl+F” to open “Find and Replace” box, leave “Find what” as blank, select “Values” from “Look in” drop down and click on “Find All” button.

Find Blank Rows

After clicking on “Find All” button, below the “Find All” button, some data is appearing. Click on any data and press “Ctrl+A” to select all data set.

Select Blank Rows

 

Now close “Find and Replace” box and the following image will appear:-

Find Blank Rows

Go to “Home” tab–>”Delete” tab–>”Delete Sheet Rows” and all blank rows deleted.

3: Go To Special Option

In this method, we will use “Go to Special” feature in Excel to find blank rows.  First, we need to select the data where to find blank rows.

Find Blank Rows

Now go to “Home” –>editing tab–>”Find & Select–>Go to Special, See image below:-

Remove Blank Rows

Click on “Go to Special”, click on “Blanks” and press “OK”

Remove Blank Rows

 

After clicking “OK” to select all blank rows. See image below:-

Find Blank Rows

Go to “Home” tab–>”Delete” tab–>”Delete Sheet Rows” and all blank rows deleted.

4: Excel Filter Option

This is another useful method to find the blank rows. Select all data where we have to find blank rows. See image below:-

Find Blank Rows

Go to “Home”–>Editing tab “Sort & Filter”–>”Filter” and click on filter:-

Apply Filter

Click on any filter drop down and uncheck the “select all” checkbox; tick “Blanks”.

Select Blank Rows

Press “OK”. See image below:-

Select Blank Rows

Blank rows are highlighted, go to “Home”–>”Delete”–>”Delete Sheet Rows” and remove the filter, we are done.

5: VBA Macro Option

VBA is another way to remove blank rows in Excel, copy the below code into the VBA Module and press “F5” to run the code.

Sub Delete_blank_rows()
Dim x As Long

With ActiveSheet
For x = .Cells.SpecialCells(xlCellTypeLastCell).Rows To 1 Step -1

If WorksheetFunction.CountA(.Rows(x)) = 0 Then
ActiveSheet.Rows(x).Delete

End If
Next x
End With

End Sub

Things to remember – Never remove blank rows by selecting blank cells

I hope you like this article of “Remove Blank Rows” in Excel. Please put your comments in the comment box below.

 

Regards

Narendra

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.