It is normally much quicker to use AutoFilter rather than loop Ranges
The code below creates a working column, then use a formula to detect delete criteria and then autofilter and delete the result records
The working column puts a formula
=OR(L1="ABC",AA1<>"DEF")
into row 1 of the first blank column then copies down as far ar the true used range. Then any TRUE records are quicklly deleted with AutoFilter
Sub QuickKill()
Dim rng1 As Range, rng2 As Range, rng3 As Range
Set rng1 = Cells.Find("*", , xlValues, , xlByColumns, xlPrevious)
Set rng2 = Cells.Find("*", , xlValues, , xlByRows, xlPrevious)
Set rng3 = Range(Cells(rng2.Row, rng1.Column), Cells(1, rng1.Column))
Application.ScreenUpdating = False
Rows(1).Insert
With rng3.Offset(-1, 1).Resize(rng3.Rows.Count + 1, 1)
.FormulaR1C1 = "=OR(RC12=""ABC"",RC27<>""DEF"")"
.AutoFilter Field:=1, Criteria1:="TRUE"
.EntireRow.Delete
On Error Resume Next
'in case all rows have been deleted
.EntireColumn.Delete
On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…