Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

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;

1

2

3

4

5

6

7

8

9

10

11

12

Sub DeleteBlackCell()

Dim Rng As Range

Dim WorkRng As Range

On Error Resume Next

xTitleId = "KutoolsforExcel"

Set WorkRng = Application.Selection

Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)

Set WorkRng = WorkRng.SpecialCells(xlCellTypeBlanks)

If Err = 0 Then

WorkRng.EntireRow.Delete

End If

End Sub

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.

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc