jeudi 2 avril 2015

Filtering in VBA in excel and is there a way to better optimize it?

I have the below code that I got from recording a macro but I find it to be a bit slow or maybe that is the way it is supposed to be not sure. I read somewhere that the way macros are recorded may not be the most efficient way and hence the reason for my question



Sub CareFilter()

Sheets("Main").Select
On Error Resume Next
ActiveSheet.ShowAllData
ActiveSheet.Range("$A$1:$AX$10000").AutoFilter Field:=23, Criteria1:=Array( _
"LATE", "LATP/F", "PAY-"), Operator:=xlFilterValues
ActiveSheet.Range("$M$1:$M$10000").AutoFilter Field:=13, Criteria1:=Array( _
"ATHRLE", "ATOCEE", "ATOCSE", "ATWCBC", "ATWCBE", "ATWCSE", "BLBSGE", "BLWCCE", _
"BRBESE", "BRBSAB", "BRBSAE", "BRBSGE", "BRBWRE", "BRCBSE", "BRCCBE", "BRCCCE", _
"BRCIS1", "BRCISE", "BROCSE", "BRSCBE", "BRWBSE", "BRWCBE", "BUBSGE", "BUOCBX", _
"BUOCSE", "BUWCBE", "BUWCSE", "HMCCBE", "KTCCBE", "KTCCOE", "KTCTCE", "KTMCCE", _
"KTOCE2", "KTOCE3", "KTOCEE", "KTPRSE", "KTRMTE", "KTSDCE", "KTSOOE", "KTWCSE", _
"MNATCE", "MNATRE", "MNCCOE", "MNHRLE", "MNMCCE", "MNPSSE", "MTBESB", "MTBSAB", _
"MTBSGB", "MTBSMB", "MTCBSB", "MTCHDB", "MTCTCB", "MTOCEB", "MTSCBB", "MTWBEB", _
"MTWBLB", "MTWBSB", "MTWCBB", "MTWCCB", "MTWCSB", "OTCIS1", "OTCISE", "OTOCSE", _
"OTSCBE", "OTSCSB", "OTWCBE", "PKCCBE", "PKCHDE", "PKHRLE", "PKLCBE", "PKLWBE", _
"PKOCSE", "PKPRIE", "PKPRSE", "PKSCBE", "PKWCBE", "PKWCSE", "YMBSAB", "YMBSAE", _
"YMBWRE"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$AW$10000").AutoFilter Field:=21, Criteria1:="<>"
ActiveSheet.Range("$A$1:$AW$10000").AutoFilter Field:=22, Criteria1:=Array( _
"AWOL", "LATE", "LATF", "LATP", "OVAB", "PAY-"), Operator:=xlFilterValues
ActiveSheet.Range("$A$1:$BD$10000").AutoFilter Field:=21, Criteria1:=">" & 1 / 10 ^ 10
Range("A1").Select
End Sub


The macro does exactly what I need it to do just hoping there may be a quicker way to achieve the same results. I do have a few of these macros that are doing different things.


Any help would be appreciated and thanks in advance.


Aucun commentaire:

Enregistrer un commentaire