How to Delete Row If Cell Is Blank in Excel (4 Methods)

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.

Download Practice Workbook

Download the following practice workbook and practice along with that.

4 Ways to Delete Row If Cell Is Blank in Excel

Now, you can delete a row manually if you find any blank cell 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:

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.

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

3. Then, click on Special.

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

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

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

6. Now, select the Entire Row radio button.

7. Then, click on OK.

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

Read more: How to Delete Specific Rows in Excel

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

Now, you can use this method too 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.

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.

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

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

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

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

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

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

Read more: How to Delete Multiple Rows in Excel

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

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

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

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

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

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

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

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

9. Now, select all the rows.

10. Next, right-click on the mouse. Then, 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.

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

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

Read more: How to Delete Rows in Excel

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.

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.

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

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

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

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

Read more: How to Use VBA to Delete Empty Rows in Excel

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. And don’t forget to check our website Exceldemy.com for more Excel-related articles.

Related Articles

• How to Count Blank Cells in Excel (5 Ways)
• Use Macro To Delete Rows Based on Criteria in Excel (3 Ways)
• How to Delete Blank Cells in Excel and Shift Data Up
• Delete Multiple Rows in Excel with Condition (3 Ways)
• How to Delete Selected Rows in Excel(8 Approaches)
• Delete Multiple Rows in Excel with VBA (4 Ways)
• VBA Macro to Delete Row if Cell Contains Value in Excel (2 Methods)