How To Delete Cells That Contain Certain Word

Sometimes, when we working with excel we need to delete cells that contain certain word. In this section, we will create excel vba macro code to delete cells that contain certain word.

Firstly, we have to create a function that we need for our next procedure. This function will count number of certain word in active sheet.

Function countText(ByVal searchText As String)
Dim rS As Range, counter As Long

counter = 0
Set rS = Cells.Find(searchText, LookIn:=xlValues, LookAt:=xlWhole)

If Not rS Is Nothing Then
fAddress = rS.Address
Do
counter = counter + 1
Set rS = Cells.FindNext(rS)
Loop While Not rS Is Nothing And rS.Address <> fAddress
End If

countText = counter
End Function

And then, here is the main part of the procedure that will delete cells that contain certain word.

Sub DeleteCells(ByVal sText As String)
Dim r As Range

While countText(sText) > 0
Set r = Cells.Find(sText, LookIn:=xlValues, LookAt:=xlWhole)

If Not r Is Nothing Then
r.Delete
End If
Wend
End Sub

Here is an example how to use the procedure above. This example will delete cells that contain word "blablabla" in active sheet.

Sub TheExample()
DeleteCells ("blablabla")
End Sub

Thank you for reading this article How To Delete Cells That Contain Certain Word With URL https://x-tutorials.blogspot.com/2009/02/how-to-delete-cells-that-contain.html. Also a time to read the other articles.