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

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

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:-

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

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:-

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:-

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

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

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:-

“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”.

After deleting the blank rows, remove the filter.

## (C): ISBLANKFormula 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:-

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:-

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

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.

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.

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

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.

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

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

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

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:-

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

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

Press “OK”. See image below:-

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

Regards

Narendra

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