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.
Sub deleteSpecificRow() Rows(4).Delete End Sub
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.
Sub deleteMultipleRows() Rows("2:4").Delete End Sub
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.
Sub deleteSelectedRows() Selection.EntireRow.Delete End Sub
Delete blank rows
In this example, Excel identifies which cells are blank, selects them and removes the rows in which they are present.
Sub deleteBlankRows() Selection.SpecialCells(xlCellTypeBlanks).Select Selection.EntireRow.Delete End Sub
This macro deletes rows from selection only if it contains the phrase “Bad Row”.
Remember that text is case-sensitive.
Sub deleteRowswithSelectedText() For Each Cell In Selection If Cell.Value = "Bad Row" Then Rows(Cell.Row).ClearContents End If Next Selection.SpecialCells(xlCellTypeBlanks).Select Selection.EntireRow.Delete End Sub
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 the contents of the cell. We can’t delete the cell because the cell position will change and some of the “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.
Cell.Value = "Bad Row"
InStr(Cell, "Bad Row") > 0
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.
Sub deleteifString() Selection.SpecialCells(xlCellTypeConstants, 2).Select Selection.EntireRow.Delete End Sub
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.
Delete even/odd rows
Now let’s delete only those rows that are even.
Sub deleteEven() For Each Cell In Selection If Cell.Row Mod 2 = 0 Then Rows(Cell.Row).ClearContents End If Next Selection.SpecialCells(xlCellTypeBlanks).Select Selection.EntireRow.Delete End Sub
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, …).