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,
) 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
- 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 :)