Here is that code:
Code:Sub PruneTradeArea() Dim lngLastRow As Long Dim lngRow As Long Dim rngFW As Range Dim cel As Range Dim rng3 As Range Dim rngFound As Range Dim lngDeleted As Long 'Debug.Print Now lngLastRow = Range("A1048576").End(xlUp).Row With Application .EnableEvents = False .ScreenUpdating = False .Calculation = xlCalculationManual End With For lngRow = 2 To lngLastRow Set rng3 = Range(Cells(lngRow, "F"), Cells(lngRow, "H")) ' Column FF is used temporarily to store the "delete" flag For Each cel In Range("Forbidden").Rows Set rngFound = rng3.Find(What:=cel, MatchCase:=False, LookAt:=xlPart) If Not rngFound Is Nothing Then Cells(lngRow, "FF") = "delete" lngDeleted = lngDeleted + 1 End If Next Next Range("FF2:FF" & lngLastRow).AutoFilter Field:=1, Criteria1:="delete" 'delete rows that are visible Application.DisplayAlerts = False Range("FF2:FF" & lngLastRow).SpecialCells(xlCellTypeVisible).Delete Columns("FF:FF").Delete Application.DisplayAlerts = True MsgBox lngDeleted & " rows deleted", vbInformation + vbOKOnly, "Results of Pruning" With Application .Calculation = xlCalculationAutomatic .EnableEvents = True .ScreenUpdating = True End With 'Debug.Print Now End Sub




Reply With Quote