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 |
|
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
Hello! What I want is similar to your example here but a tiny bit more complex! In a table (it is an order form for fruits actually) with X rows and 3 columns which are : PRODUCT , QUANTITY in pieces , QUANTITY in kilograms (so that client if eg wants pineapples he will order 3 pieces, but if he wants potatoes he will order 15 kilograms) , How can I automatically erase all the rows in which clients doesn’t want any of the product at all either in pieces, either in kilograms? Thank you! Also it would be great… Read more »
Comment awaiting moderation