I have an [af]*[/af] 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 sec.
Does anyone have experience or an explanation for this?
Why should the Formating affect Filtering so much?
Options for improving speed of autofilter?
NOTE:
I don't know if, or why it would be a factor, but note that I am using [dr]*[/dr] and VBA to expand/contract the formulas
and formating to size of the list/table. Although this is not done during the filtering use.
Here is the dynamic formating code
Sub DynFmt_List()
Application.ScreenUpdating = False
With Application
.Calculation = xlManual
End With
'///DEFINE DYNAMIC Format List RANGE
'/// dynamic range adapts to width and length of list, +501 rows
ActiveWorkbook.Names.Add Name:="zdynFmtList", RefersTo:= _
"=OFFSET(ObjectList!$I$13,0,0,501+MATCH(""*"",ObjectList!$M$13:$M$20000,-1),COLUMNS(ObjectList!$I$13:$AB$13))"
'/// Clear the formats for whole list
Range("I15:AB20000").Select
Selection.ClearFormats
'/// Copy the 'seed' format from the 'top' two rows of list
Range("I13:AB14").Select
Selection.Copy
Application.Goto Reference:="zdynFmtList"
Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Display More
I am wondering if some strange 'artifact' of manipulating the formating is becoming a factor ??
Thanks in advance for your responses.
Dave