Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilter code
Hi all,
I'm using Excel2k3 on WinXP. I'm using the following code I pieced together from Debra's site: Private Sub btnBatchAll_Click() If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("A1").AutoFilter End If MsgBox ("test") If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If End Sub Private Sub btnBatchBlank_Click() If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("A1").AutoFilter End If Columns("A:J").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Range("A1").AutoFilter Field:=9, Criteria1:="=" End Sub Each block is tied to its respective option button on an excel sheet. If I click "BatchBlank" it filters correctly. If I then click "BatchAll" it correctly shows all data. However, I am having problems after filtering via the AutoFilter arrows on a separate column. The filter works fine, but then I want to show all data again via the "BatchAll" button. Since the button is already "yes", I click the "BatchBlank" button, which works fine. However, upon clicking the "BatchAll" button, Excel takes a couple of minutes to show all the data. Can someone please tell me why? I'm working with about 100 rows of data, 10 columns. Thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilter code
Just a guess...
I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. Dominic LeVasseur wrote: Hi all, I'm using Excel2k3 on WinXP. I'm using the following code I pieced together from Debra's site: Private Sub btnBatchAll_Click() If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("A1").AutoFilter End If MsgBox ("test") If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If End Sub Private Sub btnBatchBlank_Click() If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("A1").AutoFilter End If Columns("A:J").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Range("A1").AutoFilter Field:=9, Criteria1:="=" End Sub Each block is tied to its respective option button on an excel sheet. If I click "BatchBlank" it filters correctly. If I then click "BatchAll" it correctly shows all data. However, I am having problems after filtering via the AutoFilter arrows on a separate column. The filter works fine, but then I want to show all data again via the "BatchAll" button. Since the button is already "yes", I click the "BatchBlank" button, which works fine. However, upon clicking the "BatchAll" button, Excel takes a couple of minutes to show all the data. Can someone please tell me why? I'm working with about 100 rows of data, 10 columns. Thanks. -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Autofilter code
Dave,
Worked brilliantly, thanks. "Dave Peterson" wrote: Just a guess... I'd turn calculation to manual, then do the work, then change the calculation back to whatever it was before. In fact, there are a few things that can slow down macros. I do this kind of thing to speed them up: Option Explicit Sub testme() Dim CalcMode As Long Dim ViewMode As Long Application.ScreenUpdating = False CalcMode = Application.Calculation Application.Calculation = xlCalculationManual ViewMode = ActiveWindow.View ActiveWindow.View = xlNormalView ActiveSheet.DisplayPageBreaks = False 'do the work 'put things back to what they were Application.Calculation = CalcMode ActiveWindow.View = ViewMode End Sub Your code will replace the "'do the work" line. Dominic LeVasseur wrote: Hi all, I'm using Excel2k3 on WinXP. I'm using the following code I pieced together from Debra's site: Private Sub btnBatchAll_Click() If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("A1").AutoFilter End If MsgBox ("test") If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData End If End Sub Private Sub btnBatchBlank_Click() If Not ActiveSheet.AutoFilterMode Then ActiveSheet.Range("A1").AutoFilter End If Columns("A:J").Sort Key1:=Range("C2"), Order1:=xlAscending, Header:= _ xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _ DataOption1:=xlSortNormal ActiveSheet.Range("A1").AutoFilter Field:=9, Criteria1:="=" End Sub Each block is tied to its respective option button on an excel sheet. If I click "BatchBlank" it filters correctly. If I then click "BatchAll" it correctly shows all data. However, I am having problems after filtering via the AutoFilter arrows on a separate column. The filter works fine, but then I want to show all data again via the "BatchAll" button. Since the button is already "yes", I click the "BatchBlank" button, which works fine. However, upon clicking the "BatchAll" button, Excel takes a couple of minutes to show all the data. Can someone please tell me why? I'm working with about 100 rows of data, 10 columns. Thanks. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AutoFilter on Protected Worksheet Excel 2003 | Excel Discussion (Misc queries) | |||
Complicated If Then / V Lookup / Match Statement... | Excel Worksheet Functions | |||
Conform a total to a list of results? | Excel Discussion (Misc queries) | |||
VBA code to locate cell address of AutoFilter dropdown box | Excel Discussion (Misc queries) | |||
Macro for changing text to Proper Case | Excel Worksheet Functions |