Excel VBA: 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

Related posts:

• How To Replace Data Using Excel VBA Macro Code
• Find A Particular Text Within Ranges And Return Its Row Position
• Excel VBA Macro Examples: Function Procedure
• Excel VBA Macro Examples: Sub Procedure
• Excel VBA Macro Tutorial: Controlling Execution

---
If you like posts in this blog, you can to support me :)