How to Delete Row If Cell Is Blank in Excel?

Removing rows in Excel when a cell is empty helps to clean up your data, making it easier to work with and understand. It also keeps your Excel file size smaller, making it faster. This process is useful for keeping your data accurate and organized, especially when preparing charts or moving data between different programs.

In this Excel tutorial, you will learn to delete rows if the cell is blank with 4 easy methods. These methods will include some of Excel’s built-in features and VBA code.

In the following image, you can see we have some rows with blank cells. It also shows the result after deleting those rows with blank cells.

To delete rows if the cell is blank, here are the 4 methods:

Using Go To Special Command

Go To Special in Excel is a feature that allows you to quickly select specific types of cells, such as blanks, formulas, or constants, for efficient editing and organizing. This method is highly efficient for deleting rows with any blank cells, as it easily identifies and selects all blanks in a dataset, saving significant time and reducing errors in large datasets.

To delete rows if the cell is blank with the Go To Special command, follow these steps:

Select the whole data
Then go to Home tab > Editing group > Find & Select > Go To Special.

Go To Special dialog box will appear.
From the Go To Special dialog box, select Blanks. Then click OK.

After that, the blank cells will be highlighted.
Select any of the blank cells and right-click on it. Now, click on Delete.
Delete dialog box will appear.
Now, select Entire row and click on OK.

Finally, you will see all the rows that have blank cells will be deleted.

Using Find and Replace Feature to Identify Rows and Delete Those

Find and Replace in Excel is a feature that allows you to search for specific content within a worksheet and replace it with different content. Unlike the ‘Go To Special’ method, which directly selects blank cells for deletion, Find and Replace can be used to initially mark or fill blank cells with a specific value or text, making them easier to identify.

To delete rows if the cell is blank using Find and Replace Feature, follow these steps:

Select all the data.
Press Ctrl+F on your keyboard.
After that, you will see a Find and Replace dialog box.
Click on Options.
Now, from the Find and Replace Dialog box:
- Leave it empty in the Find What box
- Select Match entire cell contents.
- Select Values from the Look in dropdown menu.
- Click on Find All.
It will bring up the lists of all those rows containing blank cells on them
Press Ctrl+A to select all those rows.
It will highlight all those blank cells.
Similar to the previous method, right-click on a blank cell. Click on Delete > Entire row > OK.

After that, it will delete all those rows having blank cells.

Filtering Rows with Blank Cells and Deleting Those

In this method, you add a temporary column (helper column) to identify rows with blank cells using a formula, and then filter and delete these rows based on the criteria set in the helper column.

Deleting rows with blank cells when you need specific control over which rows to remove, especially in complex datasets where reviewing rows before deletion is necessary.

Follow these steps to filter rows if the cell is blank and delete those:

Create a Helper column.
Now, in the Helper column:
- Write the formula: =IF(COUNTBLANK(B7:E7),”Blank”,”Not Blank”)
- Press Enter.
- Drag down the Fill Handle.
Now, go to the Data tab > Sort & Filter group > Filter.
Click on the Dropdown of the Helper column. Then, select Blank.

You will see the rows that have blank cells.
Select these Rows and right-click on your mouse. Click Delete Row.

After that, it will remove all the rows if any cell is blank in Excel.

Using VBA

Using VBA to delete rows with blank cells is beneficial as it allows for automation of the process, making it highly efficient for repetitive tasks and customizable to handle complex criteria across large datasets.

To delete rows if the cell is blank using VBA, follow these steps:

Go to the Developer tab.
Click on Visual Basic.
Now, select Insert > Module.

In the code editor, insert the following code:

Sub RemoveRows()
On Error Resume Next
Range("B6:E15").SpecialCells(xlBlanks).EntireRow.Delete

End Sub

Now, click on Run or you can press the F5 button.

After that, all the rows containing blank cells will be deleted.

Download Practice Workbook

Download the following practice workbook and practice along with that.

Delete Rows with Blank Cells.xlsm

Conclusion

In conclusion, Excel offers several efficient methods like ‘Go To Special’, ‘Find and Replace’, using a helper column, and VBA scripting to delete rows if a cell is blank. These methods help keep your spreadsheets clean and organized, whether you’re working with small or large amounts of data. Leave us a comment if you find this article helpful and also check our website Exceldemy.

Frequently Asked Questions

How Do I Remove Blank Cells From Rows in Excel?

To remove blank cells from rows in Excel, you can follow these steps:

Select the range where you want to delete rows.
Go to the “Home” tab, click “Find & Select” in the editing group, then choose “Go To Special“.
Select “Blanks” and click “OK”.
This highlights all blank cells in the selected range.
Right-click on one of the highlighted cells, choose “Delete”, then “Shift cells up“.

How Do I Remove Blank Cells From a Column?

In the selected column, use “Go To Special” > “Blanks”, right-click on highlighted cells, and choose “Delete” > “Shift cells up” to remove blank cells and shift the remaining cells up.

Can I Recover Rows After I’ve Deleted Them in Excel?

Rows can be recovered immediately after deletion using the Undo function (Ctrl + Z). However, once the workbook is saved and closed, it’s not possible to recover the deleted rows without a previous backup.

<< Go Back to Delete Multiple Rows Based on Condition | Delete Rows | Rows in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!