Find and delete column

Question

I want to find text in a column and if found delete the corresponding column.

It deletes the first found column but then errors on next iteration.

When I delete the column the FindNext method gives an error.

Unable to get the FindNext property of the Range Class

Private Sub ClearInPlanCells(strSearch As String, wrkSheetName As String)
    Dim rngFound As Range
    Application.ScreenUpdating = False
    
    With Worksheets(wrkSheetName).Cells
        Set rngFound = .Find(strSearch, LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFound Is Nothing Then
            strAddr = rngFound.Address
            Set rngFound = .Find(strSearch, LookIn:=xlValues, lookat:=xlWhole)
           ' On Error Resume Next
            Do
                'MsgBox (rngFound.Address)
                Sheets(wrkSheetName).Columns(rngFound.Column).EntireColumn.Delete
                Set rngFound = .FindNext(rngFound)
            Loop While rngFound.Address <> strAddr
        End If
    End With
    Application.ScreenUpdating = True
    End
End Sub

Answer 1

You can't do FindNext(After:=rngFound) because you've deleted rngFound. If you step through, you'll see rngFound turns into <object required> after you delete it. I suggest saving all of the columns into a variable and then deleting them after the loop.

Sub ClearInPlanCells(strSearch As String, wrkSheet As Worksheet)
    
    Dim rngFound As Range
    Dim DeleteColumns As Range
    
    Application.ScreenUpdating = False
    
    With wrkSheet.Cells
        Set rngFound = .Find(strSearch, LookIn:=xlValues, lookat:=xlWhole)
        If Not rngFound Is Nothing Then
            Dim strAddr As String
            strAddr = rngFound.Address
            
            Do
                If DeleteColumns Is Nothing Then
                    Set DeleteColumns = rngFound.EntireColumn
                Else
                    Set DeleteColumns = Union(DeleteColumns, rngFound.EntireColumn)
                End If
                
                Set rngFound = .FindNext(rngFound)
            Loop While rngFound.Address <> strAddr
            
        End If
        
    End With
    
    Application.ScreenUpdating = True
    
    If Not DeleteColumns Is Nothing Then DeleteColumns.EntireColumn.Delete

End Sub

I changed parameter wrkSheetName As String into wrkSheet As Worksheet because it makes more sense to pass the worksheet object into the sub directly, and avoid the issue of not knowing which workbook the sheet name belongs to.