Deleting a row with VBA

In Excel, you can delete a row by right-clicking on the number of the row and then selecting Delete. But when you want to delete rows using more sophisticated methods you have to use VBA. It will allow you to delete rows in many different ways.

Delete a specific row

The most basic way to delete a row in VBA is by using the row number.

This will delete the fourth row each time you execute the macro.

Delete multiple rows

You can also delete a few rows at once. Instead of selecting the row number, select a range of rows.

It will delete rows 2, 3 and 4.

Delete selected rows

So far we created macros that deleted rows with fixed numbers inside the code. This time, let’s delete rows from a selection.  Let’s use the following code.

Delete blank rows

In this example, Excel identifies which cells are blank, selects them and removes the rows in which they are present.

This macro deletes rows from selection only if it contains the phrase “Bad Row”.

CAUTION

Remember that text is case sensitive.

Code analysis

2. The loop goes through each element in the selection.

3. Excel checks the actual cell value and if the value is exactly “Bad Row”.

4. If the condition is met, Excel clears contents of the cell. We can’t delete cell because the cell position will change and some of “Bad Rows” may stay.

7. When all “Bad Rows” are cleared then Excel selects these cells.

8. Then deletes rows of these cells.

If you also want to delete those rows that consist of this phrase, and not only the exact match, use the InStr function. This function returns the position of the first occurrence of the phrase.

Change

to

We have four different examples inside our worksheet. Each of them returns a position.

  • “Bad Row” returns 1
  • “Good Row” returns 0
  • “1 Bad Row” returns 3
  • “Bad Row !” returns 1

That means that all rows but “Good Row” will be removed.

Delete row where the cell type is a string

In this example, we will delete a row if the cell type is a string.

At first glance, cells A3, A4, and A5 seem to be of the same type. But when you look at what is going on behind the scenes (Ctrl + `) you will see that each of them is different.

In the second line, you have the following code: xlCellTypeConstants. It means that we are searching for constants. Number 2 means the second type, which is text.

Excel will choose only values of type text.

The result.

Delete even/odd rows

Now let’s delete only those rows that are even.

The code is very similar to that from the previous example. There is only one difference.

Cell.Row Mod 2 = 0

This means, if the remainder after division is 0 (for numbers: 2, 4, 6, 8, …) then the number is even, if it’s 1 then it’s an odd number (1, 3, 5, 6, …).