Excel VBA to Delete Row If Cell Contains Partial Text (4 Examples)

Get FREE Advanced Excel Exercises with Solutions!

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.

Showing how to go to Visual Basic from the Developer tab

  • 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.

Inserting a new Module

  • 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.

Dataset to be used for all methods


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.

VBA Code for deleting rows with Range.AutoFilter

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.

Showing result after deleting all target rows


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.

VBA Code to delete rows using the InsTr function

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.

Showing results after deleting rows that contain partial text

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.

VBA Code for excel to delete row if cell contains partial text with For loop

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.

Showing result after deleting rows that contain partial text


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.

VBA code to delete row with InputBox

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.

Showing result after rows are deleted


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.

Dataset to be used for deleting rows if a cell has specific text

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.

VBA code to delete rows if a cell has specific text

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.

Showing result after cells with specific text have been deleted

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.

Dataset to be used for deleting rows with blank cells

Copy the following Code in a new Module and Click on the Run button.

VBA code for deleting rows with blank cells

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.

Showing result after the rows 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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Sourav Kundu
Sourav Kundu

Sourav Kundu, BSc, Naval Architecture & Marine Engineering, Bangladesh University of Engineering and Technology, is a dedicated technical content creator of the ExcelDemy project. He has a keen interest in Excel and he leverages his problem-solving skills to provide solutions on user interface with Excel. In his position as an Excel & VBA Content Developer at ExcelDemy, Sourav Kundu not only adeptly addresses challenging issues but also demonstrates enthusiasm and expertise in navigating complex situations. Apart from creating... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo