Delete a particular string defined in Range of Columns across sheets

[jack29]

Hi ,

I have managed to write this VBA code, where in to delete the string named "Do Nothing", across all the sheets in the workbook. code without looping each sheet it's working. However, After Inclusion to loop across the worksheets, it's failing to loop through all the sheets, instead only deletes the only sheet which is active. Please kindly help me with the correction of this code to delete a string that has a string "Do Nothing", across all the sheets in a specified Range from K2 to K Last.

Sub LoopThroughSheetsDeleteString()

'Dim wb As Workbook
Dim ws As Worksheet

For Each ws In Worksheets

    
    Dim rng As Range
    Dim cell As Range
    Dim ContainWord As String

    
    Set rng = Range("K2:K30")

    
    ContainWord = "Do Nothing"

    
    For Each cell In rng.Cells
        If Not cell.Find(ContainWord) Is Nothing Then cell.Clear
    Next cell
Next ws
End Sub

 

[Fluff]

Try it like

Sub LoopThroughSheetsDeleteString()

'Dim wb As Workbook
Dim ws As Worksheet

For Each ws In Worksheets

    
    Dim rng As Range
    Dim cell As Range
    Dim ContainWord As String

    
    Set rng = ws.Range("K2:K30")

    
    ContainWord = "Do Nothing"

    
    For Each cell In rng.Cells
        If cell.Value = ContainWord Then cell.Clear
    Next cell
Next ws
End Sub

 

[jack29]

It worked thank you @Fluff

 

[Fluff]

You're welcome & thanks for the feedback.