Macro to delete rows used to work fast, but now it has become slow

[manojrf]

Hi there,

Dim LastRow&, FilterRange As Range
With Sheets("KCC - OD")
.AutoFilterMode = False
LastRow = .Cells.Find("*", after:=.Range("B1"), SearchOrder:=xlByRows, searchdirection:=xlPrevious).Row
Set FilterRange = .Range("AB1:AB" & LastRow)
FilterRange.AutoFilter Field:=1, Criteria1:="=2"
On Error Resume Next
FilterRange.SpecialCells(12).EntireRow.Delete
Err.Clear
.AutoFilterMode = False
End With
Set FilterRange = Nothing

First of all let me tell you that I am not an expert in Excel or making macros. I get the knowledge from the members of this forum who are ready to help.

I am using the macro given above to delete rows in a sheet named KCC - OD, when the value in column AB is 2. It was working fine and fast. But nowadays it is **** slow and I don't know why. The sheet has around 2000 rows and 30 columns.

Can anyone help me to make it a bit more fast.

Thank you.

 

[jkpieterse]

Have calculation-intensive formulas that point to that table been added recently by any chance?

 

[manojrf]

Have calculation-intensive formulas that point to that table been added recently by any chance?

There is a formula in the cells in column AB and I have been using it for some years.

Thanks

 

[kvsrinivasamurthy]

Try

Sub Macro1()
Dim Lr&, T&, S$
Dim M, b
Application.ScreenUpdating = False
With Sheets("KCC - OD")
Lr = .Range("B" & Rows.Count).End(xlUp).Row
M = Filter(Evaluate("Transpose(If('KCC - OD'!AB1:AB" & Lr & "=2,""A""&Row(AB1:AB" & Lr & "),False))"), False, False)
For T = UBound(M) To 1 Step -1
S = S & "," & M(T)
If Len(S) > 240 Or T = 1 Then
.Range(Mid(S, 2)).EntireRow.Delete
S = ""
End If
Next T
End With
Application.ScreenUpdating = True
End Sub