5 Ways to Delete Blank Rows in Excel (With VBA Macro)

In this blog post, we will learn about removing/delete blank rows. Blank rows are not a good sign in the dataset, can be disturbed at the time of calculation.

Here we will learn “5” easiest methods to get rid of blank Rows in Excel.

First of all, we must share the dataset image of our dataset in which we are going to work. See the image below.

This dataset is containing some partial blank rows and some complete blank.

Partial blank rows are necessary because containing some data, so no need to remove these rows.

But complete blank rows have no meaning in the dataset, so need to delete these rows.

Here in this tutorial, with the help of some useful Excel Function, we will remove these complete blank rows.

In a small dataset, we can delete these blank rows easily one by one but it is not possible in a huge dataset.

So here, we have many ways describe to delete unwanted completely blank rows.

First Method: With Formulas (Delete Blank Rows)

Excel COUNTBLANK Formula

So the first method, we use the Excel COUNTBLANK Function which helps to count the blank cells in a row.

We need to convert this dataset into a table. And then we will insert a new column after the last column of the dataset.

So in the last inserted rows, we will apply the Excel COUNTBLANK Function to count blank cells in the whole rows.

The maximum occurrence of blank cells, will be considered as the complete blank rows.

Then we can apply the filter option to select all these blank rows and delete them.

For converting the dataset in a table format, select the whole dataset and press the keyboard shortcut command “Ctrl + T”.  Using this command will convert our dataset into a table format.

Converting the Excel data into a table is much easier for calculating the data and updating the data at once.

See the image below:-

Using this command, a small “Create Table” pop-up opens. Just press “Ok” here.

See our dataset has been converted into a table format, See the image below:-

Now at the end of the new dataset, we will insert a new column and name it “Blank”. See the image below:-

Now apply the Excel COUNTBLANK formula in the last added column “Blank” and press Enter:-

We will get the following image, where we can see that the red circled area represents the maximum of 5 nos. of cells are blank. i.e. these whole rows are blank and need to be deleted.

Here we need to apply the filter for maximum occurrence of blank cells which are red circled in the above image.

Go to filter in editing tab and click “Sort & Filter” drop-down and click on “Filter” and we will get the below image.

Now click on the drop-down of the Blank column and remove the tick from the “Select All” and then tick on the maximum figure. In this case, “5” is the maximum figure and press “OK”. Once we press “OK” we will get the below image.

Select all blank rows and go to “Home Tab” in the ribbon and then go to “Cells Group” and click on the “Delete” drop-down menu and click on “Delete Sheet Rows”.

See the image below:-

After deleting the blank rows from our dataset, our new dataset will looks as below image:-

Our new dataset is free from the unwanted blank rows.

Some cells are appearing blank but they are partial blank not completely. Which means they have some data.

Excel COUNTA Formula

This is our second method to delete unwanted blank rows from the dataset.

As we did in the previous method, convert our dataset in a table format and then apply the ‘Excel COUNTA Function’.

Excel COUNTA function counts every character as “1”, and if there is no data in any cell, it will consider as “0”.

As we know the Excel COUNTA Function, counts every single character in the cells.

See in the image below:-

If the total count is “0” at the blank column, which means this row is completely blank.

Now apply the filter here, and this time we are looking for “0” here. In this method total count “0” is the complete blank rows.

And select the blank rows and delete them in the same way.

Excel ISBLANK Formula

The Excel ISBLANK function works the same as the Excel COUNTA function. When we apply the ISBLANK function to find the blank rows in the Excel data set, keep in mind that here we press Ctrl+Shift+Enter instead of simple Enter as an array formula.

Use this formula in the following way.

This function tells us, if there is any blank cell it will give the result as “TRUE” and if not blank then “FALSE”.

Here we will filter for “TRUE” in the blank column and then again select it and delete these unwanted blank rows.

Excel SUM (LEN) Formula

Using the SUM with LEN (length) function, this function will calculate total characters in the row and sum up. But if there are no characters in any cell it will represents as “0”.

Remember that this an array formula and need to press “Ctrl+Shift+Enter” instead of simply Enter.

After applying the formula, apply filter and select “0” from the drop-down and select all rows having “0” value in the blank column. And delete blank rows and our dataset is now free from unwanted blank rows.

Second Method: Excel FIND Option

To find blank rows in the dataset, here we will use Excel FIND Function.

For that first, we will select all the datasets.

Then press “Ctrl + F” to open the Find and Replace dialogue box as and click on the “Option” button and leave “Find what” as blank, change “Look in” as “Values” and click on Find All.

Once we click on Find All button we will get the following image:-

When we will hit “Find All”, we will get some data area in the above-mentioned format. Put the cursor on the above mentioned area and press “Ctrl + A”.

After pressing “Ctrl + A”, all the blank cells will be highlighted and once all blank cells will be highlighted, close the “Find and Replace” box.

Apply filters and select the blank rows and apply the earlier procedure to delete blank rows.

Third Method: Go To Special Option

Here we will use the “Go to Special” method to delete the unwanted blank rows in the Excel dataset.

First, select all the datasets and press “F5” as a keyboard shortcut.

You can also use the “Menu” bar here to locate the “Go to Special” option.

Go to the “Home” tab then go to the “Editing” group and click “Find & Select” from the drop-down menu and click the “Go to Special” option.

See Image below:-

Once click on the “Go to Special” option, the following image will appear

Click on the “Blanks” radio button and press the “OK” button. Our blank rows will be selected in our dataset.

See the image below:-

To delete the blank rows go to the “Home” tab and then go to the “Cells” group and click on the “Delete” option and finally click “Delete Sheet Rows” and all the selected blank rows will be deleted.

Fourth Method: Excel Filter Option

Here we will use the “Filter” method to delete the blank rows in Excel. This is one of the easiest methods.

First, we will select all the Excel dataset as we did in earlier methods.

Then go to the “Home” tab and then go to “Editing” group then use “Short & Filter” click on drop-down here and select “Filter” option. See the image below:-

Once we click on “Filter”, see the following will appears.

Select any filter drop-down menu and remove checked from “Select All”. Mark checked on the “Blanks” check box at the end and press “OK”.

All the blank rows only will be displayed highlighted and then apply the deleting procedure as we did in the rest of the methods.

Fifth Method: VBA Macro Option

There is another method to “Delete Blank Rows” in Excel with the help of the VBA macro.

In this method, go to the “Developer” tab, then click on left most “Visual Basic” option, a new VBA Application window will open.

Go to the “Insert” tab and insert a new “Module” by clicking on “Module”.

Write the following VBA code in this module, see how it looks like:-

Sub Delete_blank_rows() Range(“A1:E20”).Select                  Selection.SpecialCells(xlCellTypeBlanks).EntireRow.Select          Selection.Delete End Sub

Now execute this VBA code step by step by pressing “F8” again and again.

All the blank rows will be selected and then deleted.

If the “Developer” tab is not enabled in your Excel window then just right-click anywhere in Excel  “Ribbon” and click on “Customize the Ribbon”.

Once you click on “Customize the Ribbon”, the following window will open.

Then select option “Customize Ribbon” and at the very right side list options, make sure your “Developer” option is mark checked. Then click on “OK”.

Once you click on the “OK” button, your “Developer” tab will appear at the last in your “Menu” bar.

By clicking on the “Developer” tab you can apply the VBA method to delete blank rows in your dataset.

I hope this tutorial helps you learned about deleting the blank rows in the Excel dataset.

If you have any other way to delete blank rows, please feel free to share with others.

Thanks

Narendra

Related Post

How to Wrap Text in Excel – 4 Easiest Method

How to Create a Custom AutoFill List in Excel 2013

Excel Format Painter – An Introductory Guide

How to Use Excel COUNTIFS Function with Multiple Criteria