In this article, we will show you how we can use Excel VBA to Delete Row If Cell Contains Partial Text. To delete rows with partial text, we will use VBA code to loop through each row in the specified column and check if the cell contains the specific partial text. And, if the partial text is found, the code will delete that specific row. Using VBA code we can delete those rows more efficiently and this method is particularly useful for large datasets.
How to Launch VBA Editor in Excel
To launch VBA Editor in Excel, follow these steps:
- Open Microsoft Excel.
- Then, Click on the Developer tab in the ribbon and Click on the Visual Basic icon in the Code group.
- This will open the VBA Editor Window in which the Project Explorer will be on the left-hand side and the Code Editor in the main area. Now, Select Module from the Insert tab.
- A new Module will be created where you can Write or Edit your VBA code in the Code Editor
Note:
In case the Developer tab is not visible in the ribbon, you may need to enable it by going to File > Options > Customize Ribbon and then checking the box next to Developer in the right-hand pane.
Excel VBA to Delete Row If Cell Contains Partial Text: 4 Examples
We are going to show you 4 examples to delete a row if a cell contains partial text within it. For that, we will use different inbuilt VBA commands. Also, we will show how you can perform the procedure with the InputBox feature.
We have taken this dataset that has Employee ID, Name, Salary, and Mail ID columns. We are going to delete all the rows that have hotmail.com as a common domain.
1. Using Range.AutoFilter Method
In this method, we will use the Range.Autofilter method to filter out cells that match the partial text. Then, we will delete the entire rows containing those particular cells.
For this procedure, you can Copy the following code in a new module and Click on the Run button.
Sub delete_rows_Autofilter()
Dim ws As Worksheet
Dim text As String
Dim Last_Row As Long
text = "hotmail.com"
Set ws = Sheets("Sheet2")
With ws
Last_Row = .Range("E" & .Rows.count).End(xlUp).Row
.AutoFilterMode = False
With .Range("E5:E" & Last_Row)
.AutoFilter Field:=1, Criteria1:="=*" & text & "*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
🔎 VBA Code Breakdown
Dim ws As Worksheet
Dim text As String
Dim Last_Row As Long
First, we have assigned dimensions.
text = "hotmail.com"
Set ws = Sheets("Sheet2")
Then the partial text is set as “hotmail.com” and Sheet2 is taken as the worksheet.
With ws
Last_Row = .Range("E" & .Rows.count).End(xlUp).Row
.AutoFilterMode = False
The worksheet is taken in a With statement.
The 2nd line counts the last data-filled row.
And the autofilter mode is kept inactive.
With .Range("E5:E" & Last_Row)
.AutoFilter Field:=1, Criteria1:="=*" & text & "*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
Our data starts from E5 and ends at Last_Row
AutoFilter Field:=1
this means the first column of the range.
Criteria1:="=*" & text & "*"
This part is the criteria that is used to match the partial text.
And, the next line of code is to delete all the rows that meet the criteria.
.AutoFilterMode = False
End With
And lastly, the Autofilter mode is kept inactive.
After running the code, we see that all the rows with the hotmail.com domain have been deleted and we get the output as the below image.
2. Using InStr Function
The InStr Function is used to determine the position of a text in a string. We will use the Function to delete rows that have partial text.
Copy the following code in a new Module and click on the Run button.
Sub Delete_Rows_InStr_Function()
Dim text As String
Dim LastRow As Long
text = "hotmail.com"
LastRow = Cells(Rows.count, "E").End(xlUp).Row
For i = LastRow To 1 Step -1
If InStr(1, Cells(i, "E").Value, text, vbTextCompare) > 0 Then
Rows(i).Delete
End If
Next i
End Sub
🔎 VBA Code Breakdown
Dim text As String
Dim LastRow As Long
Proper dimensions are assigned.
text = "hotmail.com"
LastRow = Cells(Rows.count, "E").End(xlUp).Row
The partial text is set as “hotmail.com” and it sets the LastRow as the last non-blank row number.
For i = LastRow To 1 Step -1
this portion initiates a For loop from LastRow.
If InStr(1, Cells(i, "E").Value, text, vbTextCompare) > 0 Then
Rows(i).Delete
The first line takes the InStr function in the If statement. It actually checks whether a certain text is present in a cell. The vbTextCompare indicates whether a certain text is present in a cell. The second line is for deleting that particular row.
After running the code, you can see all the rows with “hotmail.com” as the domain name has been deleted. The output image is given below.
Read More: How to Use Macro to Delete Rows Based on Criteria in Excel
3. Delete the Row That Contains a Partial Match with For Loop
We can also use the If statement with For Loop to find and delete rows with partial text. This loop is used for checking a condition. So, we will put our condition in the If statement and then if the condition is met then we will delete the corresponding row.
Copy the following code in a new Module and Click on the Run button.
Sub delete_rows_IF_Loop()
Dim i As Long
Dim LastRow As Long
With ActiveSheet
LastRow = .Cells(.Rows.count, "E").End(xlUp).Row
For i = LastRow To 5 Step -1
If .Cells(i, "E").Value Like "*hotmail.com*" Then
.Rows(i).Delete
End If
Next i
End With
End Sub
🔎 VBA Code Breakdown
Dim i As Long
Dim LastRow As Long
First, we have to set proper dimensions.
With ActiveSheet
LastRow = .Cells(.Rows.count, "E").End(xlUp).Row
For i = LastRow To 5 Step -1
In this portion, the first line initiates the With statement in which Activesheet is inserted.
2nd line assigns LastRow as the last non-blank row number.
The 3rd line starts a For Loop from Last to Row Number 5.
If .Cells(i, "E").Value Like "*hotmail.com*" Then
.Rows(i).Delete
This portion checks whether the cell value in Column E is “hotmail.com”. Upon meeting the criteria it deletes the corresponding row.
End If
Next i
End With
And this portion has the concluding statement.
After running the code, all the rows with the “hotmail.com” domain will be deleted as shown in the below image.
4. Taking Partial Text Through InputBox and Delete Those Rows
We can also use the InputBox to take partial text as input. We will use the same Range.AutoFilter function but will take the partial text from an InputBox.
Copy the following code in a new Module and Click on the Run button.
Sub delete_Rows_with_inputbox()
Dim text As String
Dim Last_Row As Long
Dim i As Long
text = InputBox("Enter the partial text to delete rows:")
Set ws = Sheets("Sheet3")
With ws
Last_Row = .Range("E" & .Rows.count).End(xlUp).Row
.AutoFilterMode = False
With .Range("E4:E" & Last_Row)
.AutoFilter Field:=1, Criteria1:="=*" & text & "*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub
🔎 VBA Code Breakdown
Dim text As String
Dim Last_Row As Long
Dim i As Long
First, we assigned dimensions to these names.
text = InputBox("Enter the partial text to delete rows:")
Set ws = Sheets("Sheet3")
the text will take the partial text from InputBox.
As our Data is in Sheet3, we have set Sheet3 as a worksheet.
With ws
Last_Row = .Range("E" & .Rows.count).End(xlUp).Row
.AutoFilterMode = False
Then, we take ws in With statement.
The Last_Row is assigned as the last non-blank row number.
And, the Autofilter Mode is kept inactive.
With .Range("E4:E" & Last_Row)
.AutoFilter Field:=1, Criteria1:="=*" & text & "*"
.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
End With
The first line selects the range that is to be filtered. The second line initiates Autofilter where Field is specified as column 1 and sets criteria for partial text. And the third line is to delete the row if the criteria are met.
AutoFilterMode = False
End With
And, we have to keep the Autofilter Mode inactive.
After running the code you can see all the rows with “hotmail.com” as the common domain have been deleted as shown in the below image.
How to Delete Row If Cell Contains Specific Text Using VBA in Excel?
Now let’s see how we can delete rows if the cell contains specific text. For this procedure, we will only use a simple If statement.
For the procedure, we have taken this dataset of different students and their obtained marks in different subjects. Also, there is a column that indicates whether a student is Qualified or Disqualified. We want to delete the records of all those students that are Disqualified.
First, we have to select the data range. So, Select the data range B5:G19.
Copy the following Code in a Module and Click on the Run button.
Sub Delete_Rows_Specific_text()
Dim i As Integer
For i = 5 To Selection.Rows.count
If Range("G" & i).Value = "Disqualified" Then
Rows(i).EntireRow.Delete
i = i - 1
End If
Next i
End Sub
🔎 VBA Code Breakdown
Dim i As Integer
For i = 5 To Selection.Rows.count
First, it takes i as an Integer and initiates a For Loop from i = 5 to a number of selected rows.
If Range("G" & i).Value = "Disqualified" Then
Rows(i).EntireRow.Delete
This portion of the code checks if the value in Column G is “Disqualified” and upon meeting the criteria, it deletes the corresponding row.
i = i - 1
End If
Next i
If a row is deleted, the next row will come up and fill the previous row. So, we have to decrease the value of i by 1.
After running the Code, you can see all the records of students who are “Disqualified” have been removed as the following image shows.
Read More: How to Apply VBA to Delete Rows with Specific Data in Excel
How to Delete Row If Cell Is Blank Using Excel VBA?
Now, let’s see how we can delete rows if the cell is blank. For this procedure, we will use the Do While loop.
For this procedure, we have taken this dataset about selling information of a company. The dataset has the Purchase Price of all products but all of them haven’t been sold. So, some of the cells in the Selling Price column are blank. And, we will delete all those records that have blank cells in Column E.
Copy the following Code in a new Module and Click on the Run button.
Sub delete_rows_if_cell_is_blank()
Dim count As Long
Dim i As Long
count = ActiveSheet.Cells(Rows.count, "B").End(xlUp).Row
i = 5
Do While i <= count
If Cells(i, 5).Value = "" Then
Rows(i).EntireRow.Delete
i = i - 1
End If
i = i + 1
count = ActiveSheet.Cells(Rows.count, "B").End(xlUp).Row
Loop
End Sub
🔎 VBA Code Breakdown
Dim count As Long
Dim i As Long
First, the Dimensions are assigned.
count = ActiveSheet.Cells(Rows.count, "B").End(xlUp).Row
This line sets the value of “Column” as the last non-blank row of the sheet.
i = 5
Do While i <= count
As our data starts from row 5, we have to set i as 5 and then operate a Do While loop up to count.
If Cells(i, 5).Value = "" Then
Rows(i).EntireRow.Delete
i = i - 1
The IF statement is initiated. It checks whether the cells in Column E are blank. And, then upon meeting the criteria, it deletes the corresponding row. Also, the value of i is decreased by 1 so that the code can test the next row which has moved up to fill the gap.
End If
i = i + 1
count = ActiveSheet.Cells(Rows.count, "B").End(xlUp).Row
Loop
If the cell is not blank, we have to increase the value of i by 1. And upon deleting a row the value of count will also change. So, we have to reevaluate the count.
After running the code, you can see that all the records with blank cells have been deleted.
Read More: How to Use VBA to Delete Empty Rows in Excel
Frequently Asked Questions (FAQs)
Here are some frequently asked questions that you can read.
- How do I Delete a row in Excel VBA with specific text?
You can delete a row in Excel VBA by Rows(i).EntireRow.Delete command. The code first specifies the row that needs to be deleted and then uses EntireRow.Delete command.
- How do I Delete a row based on a condition?
You can delete rows using conditional format, and first assign a conditional format to your data. After that, Select the rows you want to delete. Then, Click on any of the selected cells, and select ‘Delete‘.
- How do I Delete all rows containing certain data?
To delete all rows that have a certain value, you should use the filter feature. First, you have to select the column that contains the values you want to delete. Then, Click on the Data tab and Click the Filter button. In the filter dialog box, select the value you want to delete and Click the OK button.
Things to Remember
- The VBA Code used for InputBox is essentially the same as the AutoFilter We have only taken the InputBox as the text string.
- Using a proper loop is very important. Otherwise, the code could fall into an infinite loop.
- Before running the code, make sure you have backed up your data.
- You should test these codes on a small dataset before applying them to a large dataset.
Download Practice Workbook
You can download and practice this workbook.
Conclusion
So, we have shown you how you can use Excel VBA to Delete a Row If a Cell Contains Partial Text. We also have shown you how to delete rows if the cell contains a specific text and if the cell is blank. We hope you find the content of this article useful. If you have further queries or suggestions, feel free to leave them in the comment section. For further knowledge, you can visit our website ExcelDemy.com.