Monday, February 16, 2009

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
        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
    End If
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:
If you like posts in this blog, you can to support me :)

No comments:

Post a Comment