How to delete rows that contain empty cells in Excel – Excelchat

When cleaning up data for analysis, we are required to have all the cells in each of the columns filled with data. If some rows have empty cells, then we should delete those rows. But you need to learn how to delete rows if cell is empty. In this post, we shall look at how to delete empty rows in VBA.

Delete rows with empty cells

There are many ways that we can use to delete a row if cell is blank. One of the most used involves Find & Select> Go To Special > Blanks. This method is most useful if you have data that is listed in only one column.

Procedure of getting rid of rows with blank cells.

Step 1: Click on the Go To Special command

The first thing you need to do is to locate and click on the Go To Special command. To do this, just head to the Home tab and click on the Find & Select. You will then be able to see Go To Special.

Figure 1: Using Go To Special to delete rows with empty cells

Step 2: Check the blanks option

Once you have opened the Go To Special dialog box, you will be able to see many special options. Among these options, we have the “Blanks” option. Check it and click Ok. This will select all the blank cells in the column.

Figure 2: Go to special options

Step 3: Right-click and delete

Now that you have selected all the blank cells in the column of the worksheet that you want to delete rows with blank cells, the next thing you need to do is right-click on any of the selected cells. Then click on delete.

Figure 3: Click Delete

Step 4: Check Entire row

You will be presented with a Delete dialog box when you click on delete. In this dialog box, you need to check the Entire row option. Then click OK.

Figure 4: Delete window

Now go back and check. You will notice that all the rows with empty cells have been deleted.

Deleting rows with empty cells with VBA Macro

We can also use VBA macro to delete blank rows. Using VBA to delete rows works in a more similar way with the Go To Special command, with the exception that this uses a code to delete rows in.

In order for us to delete entire row with VBA, we can proceed as follows;

Step 1: Open Microsoft Visual Basic

You first need to open the Microsoft Visual basic on your in your Excel in order to perform a macro delete row. To do this, you simply need to press Alt + F11 keys simultaneously. This will open the Microsoft Visual basic window.

Step 2: Insert a module

The next thing you need to do is to insert a module. To do this, simply click on the Insert then on Module. Once there, paste the VBA Macro into that module;

Step 3: Run the VBA Macro

You now have to run the VBA macro to delete entire row with empty cells. This can simply be done by pressing the F5 key. Alternatively, you can click the Run button.

You will see a Kutools Excel dialog box. In this box, you need to select and specify the column from which you want to delete entire rows if cells are blank. Then click Ok.

Figure 5: Kutools window

Once you click Ok, all the rows that have empty cells in the specified column will be deleted at once.

Instant Connection to an Expert through our Excelchat Service

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.