Autofilter code

[Guest]

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]

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.

 

[Guest]

Dave,

Worked brilliantly, thanks.

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.