Can this bit of filter code be improved?

[tonywatsonhelp]

Hi Everyone,

I have a slow filter code that takes about 3 seconds to run

i'm woundering if it can be speed up somehow?

this is what I have at the moment

Sub Dashboard_Filter()

On Error GoTo EH
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Application.EnableEvents = False
Sheets("PDD").Columns("C:C").NumberFormat = "General"
LastrowDash1 = Sheets("Profitability Dashboard").Cells(Rows.Count, "AC").End(xlUp).Row
If LastrowDash1 < 102 Then
LastrowDash1 = 102
End If
LastCol1 = Sheets("Profitability Dashboard").Range("AB100").Value
Application.ScreenUpdating = False
Sheets("Profitability Dashboard").Range("AC102:AR" & LastrowDash1).ClearContents
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

Sheets("PDD").Range("$A2:$O2").AutoFilter

Status = Sheets("Dashboard Data1").Range("B10").Value
startM = Sheets("Dashboard Data1").Range("B12").Value
endM = Sheets("Dashboard Data1").Range("B14").Value
Client = Sheets("Dashboard Data1").Range("B18").Value
Cat = Sheets("Dashboard Data1").Range("B20").Value
Focus = Sheets("Dashboard Data1").Range("B22").Value
Project = Sheets("Dashboard Data1").Range("B24").Value
EM = Sheets("Dashboard Data1").Range("B26").Value
PM = Sheets("Dashboard Data1").Range("B28").Value
NCL = "No Capacity Logged!"

LrowFit1 = Sheets("PDD").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=13, Criteria1:="<>" & NCL

If startM <> 0 Then
Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=3, Criteria1:=">=" & startM
Else
Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=3, Criteria1:=">=01/01/2016"
End If
If endM <> 0 Then
Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=15, Criteria1:="<=" & endM
End If
If Client <> 0 Then
Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=4, Criteria1:=Client
End If
'''''''
If Status <> 0 Then
If Status = "Current/Completed" Then
Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=12, Criteria1:=Array("Current", "Completed"), Operator:=xlFilterValues
Else

Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=12, Criteria1:=Status
End If
End If


'''''

If Project <> 0 Then
Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=6, Criteria1:=Project
End If
If Cat <> 0 Then
Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=5, Criteria1:=Cat
End If
If Focus <> 0 Then
Application.ScreenUpdating = False
Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=9, Criteria1:=Focus
End If
If EM <> 0 Then
Application.ScreenUpdating = False
Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=7, Criteria1:=EM
End If
If PM <> 0 Then
Application.ScreenUpdating = False
Sheets("PDD").Range("$A$2:$O$" & LrowFit1).AutoFilter Field:=8, Criteria1:=PM
End If
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''



EH:
  Sheets("Dashboard Data1").Visible = False






'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Application.ScreenUpdating = True
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
End Sub

Any ideas would be a great help
Thanks
Tony

 

[DanteAmor]

You can try an advanced filter.
This is the code.

Sub Avaenced_Filter()
  Dim LRow As Long
  LRow = Sheets("PDD").Cells(Rows.Count, "A").End(xlUp).Row
  Sheets("PDD").Range("A2:O" & LRow).AdvancedFilter Action:=xlFilterInPlace, _
    CriteriaRange:=Sheets("Dashboard Data1").Range("A1:J2"), Unique:=False
End Sub

You need to put the criteria in row 2, check the following:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	NCL	startM	endM	Client	Status	Project	Cat	Focus	EM	PM	<--Headings with the same name of the sheet "PDD"
2											<--Here are the criteria for the filter
Dashboard Data1

 

[tonywatsonhelp]

Hi Dante,
Yes, I've just done a quick test but first result was super quick, this is a big help thank you
Oh and Happy Birthday!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

And thanks for the congratulations.