Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Filter Switches vs Calculations?
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 |
#2
|
|||
|
|||
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 wrote: 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 |
#3
|
|||
|
|||
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 -----Original Message----- 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 wrote: 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 . |
#4
|
|||
|
|||
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 wrote: 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 -----Original Message----- 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 wrote: 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 . -- Dave Peterson |
#5
|
|||
|
|||
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 -----Original Message----- 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 wrote: 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 -----Original Message----- 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 wrote: 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 . -- Dave Peterson . |
#6
|
|||
|
|||
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. Ken wrote: 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 -----Original Message----- 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 wrote: 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 -----Original Message----- 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 wrote: 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 . -- Dave Peterson . -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter Switches vs Macro? | Excel Discussion (Misc queries) | |||
Filter Switches vs Sheet Protection? | Excel Discussion (Misc queries) | |||
Pivot table and filter | New Users to Excel | |||
Advanced Filter: No Filter | Excel Discussion (Misc queries) | |||
advanced filter won't allow me to filter on bracketed text (-456.2 | Excel Discussion (Misc queries) |