Filter Switches vs Calculations?

[Ken]

Excel 2000 ... Spread sheet is 30 Cols by 5000 rows ...
Only 2 columns with simple formulas, but I do have several
columns with conditional formatting formulas.

Above said ... when I use a Column Filter Switch the
colapses like a clam ... (perfect)

Issue is ... when I use the Filter Switch "ALL" to open
the spread sheet back up ... the rows open as a clam as
well (takes a while) ... spread sheet appears to churn in
calculation mode (message from lower left of screen)

Any thoughts on how to improve this??? ... Thanks ... Kha

 

[Dave Peterson]

This may work for you...

I hit the escape key to abort the recalculation after I've unfiltered my data.

It seems that once excel determines that all the rows are shown, it can
recalculate quicker.

Watch the statusbar in the bottom left corner to see if the calculation is done.

 

[Ken]

Dave (or anyone) ... Hitting the Escape key is not
stopping the Calculations ... I Filter my list (no
issues) ... However, when I UnFilter by selecting "ALL"
the status bar reflects Calculations 100% ... then churns
here for a few moments (much longer than I wish).

However, if I switch my Calculations to "Manual" ... then
UnFilter the List ... then Calculate ... everything is
perfect ... Filtered List opens/closes in a flash when
calcuations set to "Manual".

Now I guess I am wondering why activating UnFilter (ALL)
doesn't turn off calculations ... UnFilter ... then
calculate by default???

Any more ideas or am I missing something here?

Thanks ... Kha

 

[Dave Peterson]

I'm not sure why it doesn't interrupt calculation for you.

But maybe you could use a macro that sets calculation to manual, shows all the
data and resets the calculation.

Option Explicit
Sub ShowAllMyData()
Dim CalcMode As Long
With ActiveSheet
If .FilterMode Then
CalcMode = Application.Calculation
Application.Calculation = xlCalculationManual
.ShowAllData
Application.Calculation = CalcMode
End If
End With
End Sub

 

[Ken]

Dave ... I copy/pasted your Macro into my Recorded Macro
List ... I ran it ... work perfect. My next question
is ... What's with the "Option Explicit" ... Do I need
that? I see it often when folks on the board write
Macros, but when I record Macros I never see it???

Thanks ... Kha

 

[Dave Peterson]

Option Explicit
Is a directive to the compiler that tells it that you as the programmer are
going to declare your variables.

Then if you make a typing mistake:

Dim lCtr as long
lCtr = 1Ctr + 1

You'll get an error since a mistyped variable wasn't Dimmed.

Did you see the error in my example:
One of those lCtr's is (ELL-ctr) and the other is (one)-ctr.

Without that "option explicit" the top, excel will just figure you know what
you're doing and meant to do that. But your code (probably) won't run as
intended.

These kinds of typos can be difficult to find. Forcing yourself to declare the
variables will ease this kind of debugging problem.

And a nice thing about declaring variables is you can use VBA's intellisense.
If you do:

dim Wks as worksheet
set wks = worksheets("sheet1")

then later type

Wks.
(note the dot)
You'll see a list of all the possible methods and properties that go with that
variable type (worksheet in my example).

And another nice thing...

If you have a variable like this:

Dim myCounterOfReceiptsPaidOnTime as long

You can type

mycount
and hit ctrl-spacebar.
You'll see a list of all the stuff that starts with those characters and you can
pick the one you want (or it's only this variable, it'll autocomplete for you).

And I think most people who spend anytime writing macros have this option turned
on automatically for all new modules (you'll still have to type it for existing
modules).

Inside the VBE
Tools|Options|Editor Tab|Check "Require Variable Declaration"

It's one of those things that may seem like too much work at the beginning, but
it really makes life easier when you're typing and debugging.