I have an AutoFilter list of 14,000 rows by 14 columns, and the cells have some specific formating: fill color, font,
protection status, wrap, etc....
There are an additional 7 columns of formulas to the left of the filtered range.
The strange thing is----
-WITH the formating, trying to Unfilter the list takes 2 min, via a manually activated Data>Filter>ShowAll OR via a macro run
of 'ActiveSheet.ShowAllData' .
(In an attempt to optimize speed, the VBA macro sets calculation to manual before the 'ActiveSheet.ShowAllData' and screen
updating set to false.)
-WITHOUT the cell formating (eg. by doing Edit>Clear>Formats), the ShowAll takes about 3
Does anyone have experience or an explanation for this?
Why should the Formating affect Filtering so much?
Options for improving speed of autofilter?
I don't know if, or why it would be a factor, but note that I am using Dynamic Named Range and VBA to expand/contract the
and formating to size of the list/table. Although this is not done during the filtering use.
Here is the dynamic formating code
Application.ScreenUpdating = False
.Calculation = xlManual
'///DEFINE DYNAMIC Format List RANGE
'/// dynamic range adapts to width and length of list, +501 rows
ActiveWorkbook.Names.Add Name:="zdynFmtList", RefersTo:= _
'/// Clear the formats for whole list
'/// Copy the 'seed' format from the 'top' two rows of list
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
Application.CutCopyMode = False
Application.ScreenUpdating = True
If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I am wondering if some strange 'artifact' of manipulating the formating is becoming a factor ??
advance for your responses.