Optimization needed for

[Celly]

Hello!

So I've tracked down a performance issue on a Excel 2016 project to AutoFilter calls. The example below is simplified. The workbook has a lot of worksheets which employ a lot of autofiltering. The issue seems to be that each field of the filters are added one by one, and I employ up to ten fields for some of my filters. Excel seems to get lathered from this task and shows a flickering wait cursor. The usual screenupdating and enableevents properties don't prevent the thrashing. Any thoughts for how to speed this up?

Application.ScreenUpdating = False
Application.Calculation = xlManual
Application.EnableEvents = False

' clear any filters and start over
Sheet1.AutoFilterMode = False  

[FilterRange].AutoFilter field:=1, VisibleDropDown:=False
[FilterRange].AutoFilter field:=2, Criteria1:="N", VisibleDropDown:=False
[FilterRange].AutoFilter field:=3, VisibleDropDown:=True
[FilterRange].AutoFilter field:=4, VisibleDropDown:=False
[FilterRange].AutoFilter field:=5, Criteria1:="<>0", VisibleDropDown:=False
[FilterRange].AutoFilter field:=6, VisibleDropDown:=False
[FilterRange].AutoFilter field:=7, VisibleDropDown:=False
[FilterRange].AutoFilter field:=8, VisibleDropDown:=False
[FilterRange].AutoFilter field:=9, Criteria1:="<>0", VisibleDropDown:=False

 

[Alex Blakenburg]

I have not used it but it might be worth giving advanced filtering a try.

Per Leila Gharani https://www.xelplus.com/excel-advanced-filter-trick/,
"In tests using data sets containing hundreds of thousands of records, Advanced Filter can reduce in milliseconds what traditional filters take many minutes to reduce. And I’m talking MANY minutes."

 

[Celly]

I have not used it but it might be worth giving advanced filtering a try.

Per Leila Gharani https://www.xelplus.com/excel-advanced-filter-trick/,
"In tests using data sets containing hundreds of thousands of records, Advanced Filter can reduce in milliseconds what traditional filters take many minutes to reduce. And I’m talking MANY minutes."

I don't believe the advanced filter will allow for the VisibleDropDown functionality that I am using

 

[Celly]

Many of the fields I use are just skipping columns. Removing these is problematic. The [FilterRange] named range cannot be a non-contiguous range, tried it. You can comment out the skipped fields like this:

[FilterRange].AutoFilter field:=1, VisibleDropDown:=False
[FilterRange].AutoFilter field:=2, Criteria1:="N", VisibleDropDown:=False
[FilterRange].AutoFilter field:=3, VisibleDropDown:=True
'[FilterRange].AutoFilter field:=4, VisibleDropDown:=False
[FilterRange].AutoFilter field:=5, Criteria1:="<>0", VisibleDropDown:=False
'[FilterRange].AutoFilter field:=6, VisibleDropDown:=False
'[FilterRange].AutoFilter field:=7, VisibleDropDown:=False
'[FilterRange].AutoFilter field:=8, VisibleDropDown:=False
[FilterRange].AutoFilter field:=9, Criteria1:="<>0", VisibleDropDown:=False

The filtering works as expected, but Excel is bugged and will start to show the VisibleDropDown in the wrong column. So that's not good.

 

[Celly]

So Excel is not bugged in the way I described in post 4, the VisibleDropDown defaults to true so you have to explicitly set each field to false where appropriate.

One option is to rearrange the columns to streamline this filter better

 

[Alex Blakenburg]

This won't make it any faster but using Sheet1.AutoFilterMode = False mean you are then recreating the autofilter and turning all the drop down arrows back on when you apply the filter. ActiveSheet.AutoFilter.ShowAllData would take off the filters but leave the previous drop down arrow settings intact.

You are right Advanced Filtering does not allow the use of the Drop Down arrows.

I don't have the volumes to test it on but I would imagine if you can apply the most selective filter first that it would run faster. This has nothing to do with the Column Order though. VBA doesn't care what order your statements "[FilterRange].AutoFilter field:=1, VisibleDropDown:=False" are in, they don't need to be in field number sequence. (So unless you want to reorder the fields from a visual perspective or because you are using a loop, this adds no benefit).

 

[Celly]

This won't make it any faster but using Sheet1.AutoFilterMode = False mean you are then recreating the autofilter and turning all the drop down arrows back on when you apply the filter. ActiveSheet.AutoFilter.ShowAllData would take off the filters but leave the previous drop down arrow settings intact.

You are right Advanced Filtering does not allow the use of the Drop Down arrows.

I don't have the volumes to test it on but I would imagine if you can apply the most selective filter first that it would run faster. This has nothing to do with the Column Order though. VBA doesn't care what order your statements "[FilterRange].AutoFilter field:=1, VisibleDropDown:=False" are in, they don't need to be in field number sequence. (So unless you want to reorder the fields from a visual perspective or because you are using a loop, this adds no benefit).

What I meant is reordering the columns so that fields 1,2,3,5, and 9 are together. Then I can drop fields 4,6,7, and 8.

I've attacked this from a few different directions and have made some progress.