I’ve already written about removing rows in Excel, that contain specific text. In this lesson, I’ll show you have to achieve this with a single click, instead of using all these steps.
In order to open the VBA window, press Alt + F11.
Now we have three options to remove a row.
- If the cell is the same as the string.
- Case insensitive.
- If the cell contains the string.
Our string will be called “delete”. Let’s create three macros to deal with these three problems.
If the cell is the same as the string
The following example has the words “delete” highlighted. But not all of them will be deleted. We are going to delete only those rows that consist of cells whose value equals “delete”.
Select cells from A1 to B10 and run the following code.
|
1 2 3 4 5 6 7 8 9 10 |
Sub DeleteRows() Dim rng As Range Set rng = ActiveSheet.UsedRange For i = rng.Cells.Count To 1 Step -1 If rng.Item(i).Value = "delete" Then rng.Item(i).EntireRow.Delete End If Next i End Sub |
This is going to generate the following result.
Rows with cells whose value equals “Delete” won’t be affected because our example is case-sensitive.
Code explanation:
2 and 3. We declare the range variable and then assign selected cells to this range.
5. Loop through from the last to the first cell.
6 and 7. If the value of the selected cell is exactly “delete” then remove the entire row.
Case insensitive
This example is similar to the previous one, but this time we are going to treat the uppercase and lowercase letters the same way.
It’s very easy to change. At the beginning of your code insert the following line:
|
1 |
Option Compare Text |
Now, if you run the code the result will be quite different.
If you don’t want to use Option Compare Text, you can change this line:
|
1 |
If rng.Item(i).Value = "delete" Then |
To this one:
|
1 |
If LCase(rng.Item(i).Value) = LCase("delete") Then |
It will change both strings to lowercase while comparing values.
If the cell contains the string
This is the last example. This procedure will remove every row that has the word “delete” inside. This example is case insensitive.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub DeleteRows() Dim rng As Range Dim pos As Integer Set rng = ActiveSheet.UsedRange For i = rng.Cells.Count To 1 Step -1 pos = InStr(LCase(rng.Item(i).Value), LCase("delete")) If pos > 0 Then rng.Item(i).EntireRow.Delete End If Next i End Sub |
Code explanation:
The InStr function takes two arguments and returns the position of the first occurrence of the word from the second parameter. If the word is not present then it returns 0.
If the position is greater than 0 then remove the row.
If you run this code, this is the result you are going to get this result.
