How to Delete Row If Cell Is Blank in Excel?

Get FREE Advanced Excel Exercises with Solutions!

In this tutorial, we are showing you how to delete a row if a cell is blank in Excel with suitable examples and proper illustrations. While working with a large number of data, you can find numerous blank cells in your dataset. As a result, it can misinterpret your dataset or can give you wrong information. So, you may need to delete a row if there are any blank cells. We will see the following outcome as we delete the blank rows.

Showing Overview to Delete Rows if Cells are Empty


How to Delete Row If Cell Is Blank in Excel: 4 Ways

Now, you can delete a row manually if you find any blank cells in that row. Though it will be easier, it would be almost impossible if you have tons of data in your worksheet. No wonder we need some easy techniques to handle such situations.
To delete a row after finding a blank cell, the following methods will come in handy for any dataset.

We are going to use the following dataset all through our tutorial:

Dataset for Excel Delete Row if Cell is Blank

Here, our dataset consists of some people’s names, occupations, and ages. Notice that the dataset has some blank cells. Now, our goal is to delete those rows which contain empty cells.


1. Delete a Row If a Cell Is Empty Using Go To Special Command in Excel

We use mostly this method on any worksheet. This method is effective and easy to implement.

Steps:

1. First, select the range of cells B5:D11.

Select Ranges of Cells with Blanks to Delete Row

2. Next, press Ctrl+G on your keyboard. You will see the Go To dialog box.

Go to Dialog Box to Delete Rows

3. Then, click on Special.

Select Blanks to Delete Row in Excel

4. Next, select the Blanks radio button. Then, click on OK.

After that, you will see it highlighted all the empty cells.

Highlighted Blank Row to Delete in Excel

5. Now, Right-click on any of the highlighted blank cells. Click on Delete.

Right-Click for Delete Option

6. Now, select the Entire Row radio button.

Select Entire Row Radio Button

7. Then, click on OK.

New Dataset After Deleting Rows with Blank Cells

As you can see, we have successfully deleted all rows containing empty cells in them.


2. Remove the Row If the Cell Is Blank Using the Find and Replace Command

Now, you can use this method to delete rows. This method basically finds empty cells. After that, you can delete those rows as we did earlier.

Steps:

1. First, select the range of cells B5:D11.

Select Range of Cells to Find Blank Cell

2. Next, press Ctrl+F on your keyboard. After that, you will see a Find and Replace dialog box.

3. Now, leave it empty in the Find What box. Next, click on Options.

Find and Replace Dialog Box

4. After that, select Match entire cell contents. Next, select Values from the Look in dropdown menu. Then, click on Find All.

Find and Replace Dialog Box to Delete Blank Rows

5. Here, it brings up the lists of all those rows containing blank cells on them.

List of Cells Containing Blank Cell

6. Then, press Ctrl+A to select all those rows. It will highlight all those blank cells.

Select All the Rows to Delete

7. Similar to the previous method, right-click on the mouse after that. Click on Delete > Entire row > OK.

Click on Entire Row Option

8. After that, it will delete all those rows.

Final Result after Deleting Row if Cell is Blank

In the end, we successfully removed all those rows containing blank cells.


3. Delete Row Using the Filter Command

Now, this method is a bit tricky. Here, we are using a formula to find blank cells and create another column. In that column, we are basically writing whether it is blank or not. Then, by using Excel’s Filter option we will delete those rows. Let’s see the following steps.

Steps:

1. First, create a new column “Cell“.

Create New Column "Cell"

2. Then, type the following formula in Cell E5:

=IF(COUNTBLANK(B5:D5),"Blank","No Blank")

Formula for Finding Blank Cells

3. Then, press Enter. After that, it will show whether the row has blank cells or not.

Result After Entering the Formula

4. Next, drag the Fill Handle icon over the range of cells E6:E11.

Drag the Fill Handle to Copy the Formula

5. Now, select any cell. Then, go to the Home tab. Click on Filter afterward.

Go to Data Tab for Filter Option

6. After that, a Drop-down menu will appear.

Dropdown Menu on Each Column

7. Now, click on the dropdown of the Cell column. Deselect all the options. Then, select Blank. Click on OK.

Select Blank to Filter Blank Row

8. Here, you will see, all the rows that have blank cells in them.

Row with Blank Cell in Excel

9. Now, select all the rows.

Select All the Rows to Delete

10. Next, right-click on the mouse. Then, click on Delete Row.

Click on Delete Row

11. After that, it will delete all those rows. Next, click on the drop-down of the Cell column. Then, click on No Blank > OK.

Select No Blank for Non Blank Row

12. After that, you will see the rows that have no blanks.

Row After Delete Blank Cell in Excel

As you can see, we have successfully removed all the rows containing blank cells.

Read More: How to Delete Rows in Excel with Specific Text?


4. Delete Row If Cell Is Blank Using VBA in Excel

Now, if you have knowledge of Excel’s VBA Macros then you should try this method too. This method is also effective and will save you a lot of time. So, without any delay, let’s see the following steps.

Steps:

1. First, press Alt+F11 on your keyboard. It will open VBA Editor. Click on Insert > Module.

Insert VBA Module in Excel

2. Now, in the editor, type the following code:

Sub RemoveRows()
Dim Rng As Range
Dim WRng As Range
On Error Resume Next
xTitleId = "Delete Rows If Cell is Blank"
Set WRng = Application.Selection
Set WRng = Application.InputBox("range", xTitleId, WRng.Address, Type:=8)
Set WRng = WRng.SpecialCells(xlCellTypeBlanks)
If Err = 0 Then
WRng.EntireRow.Delete
End If
End Sub

3. Then, press Ctrl+S to save.

4. Now, go to the View tab. Click on Macros.

Click on Macros in View Tab

5. After that, it will open a Macro dialog box. Click on Run.

Run VBA Code to Delete Row if Cell is Blank

6. Then, another dialog box will appear. In the range box, select the range of cells B5:D11. Click on OK.

Select Range of Cells to Delete Row if Cell is Blank in Excel

7. After that, our macros will delete all the rows with blank cells.

Using VBA Code to Show Delete Row if Cell is Blank in Excel

In the end, we were successful in deleting all the rows with blank cells.


Download Practice Workbook

Download the following practice workbook and practice along with that.


Conclusion

Here, we have discussed 4 handy ways to delete a row if a cell is empty in Excel. To conclude, I hope these methods will help you remove rows with blank cells. Learn these methods and practice all of them. Besides, you can download the practice workbook and try these yourself. Also, feel free to give your feedback in the comment section.


Related Articles

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.
A.N.M. Mohaimen Shanto
A.N.M. Mohaimen Shanto

Hello! I am Shanto. An Excel & VBA Content Developer. My goal is to provide our readers with great tutorials on various Excel-related problems. I hope our easy but effective tutorials will enrich your knowledge. I have completed my BSc in Computer Science & Engineering from Daffodil International University. Working with data was always my passion. Love to work with data, analyze those, and find patterns. Also, love to research. Always look for challenges to keep me growing.

2 Comments
  1. Great methods, thank you for publishing them!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo