Macro too slow...how to optimize

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Hi all,

This is really slow...I'm sure I can optimize...please
help! (what it does: reset all my auto filter to "ALL"
and hides a few sets of columns)

Paul

Sub prog_view()
'
' prog_view Macro

Selection.AutoFilter Field:=1
Selection.AutoFilter Field:=2
Selection.AutoFilter Field:=3
Selection.AutoFilter Field:=4
Selection.AutoFilter Field:=5
Selection.AutoFilter Field:=6
Selection.AutoFilter Field:=7
Selection.AutoFilter Field:=8
Selection.AutoFilter Field:=9
Selection.AutoFilter Field:=10
Selection.AutoFilter Field:=11
Selection.AutoFilter Field:=12
Selection.AutoFilter Field:=13
Selection.AutoFilter Field:=14
Selection.AutoFilter Field:=15
Selection.AutoFilter Field:=16
Selection.AutoFilter Field:=16
Selection.AutoFilter Field:=17
Selection.AutoFilter Field:=18
Columns("A:FR").Select
Range("FR1").Activate
Selection.EntireColumn.Hidden = False
Rows("13:13").Select
Selection.EntireRow.Hidden = True
Columns("L:Q").Select
Selection.EntireColumn.Hidden = True
Range( _
"U:X,Z:AC,AE:AH,AJ:AM,AO:AR,AT:AW,AY:BB,BD:BG,BI:BL
,BN:BQ,BS:BV,BX:CA,CC:CF,CH:CK,CM:CP,CR:CU,CW:CZ,DB:DE,DG:D
J,DL:DO,DQ:DT,DV:DY,EA:ED,EF:EI,EK:EN,EP:ES,EU:EX,EZ:FC,FE:
FH,FJ:FM,FO:FR" _
).Select
Selection.EntireColumn.Hidden = True
Columns("L:Q").Select
Selection.EntireColumn.Hidden = True
Range("A11").Select
End Sub
 
One way:

Sub prog_view()
'
' prog_view Macro
Static rng As Range
If rng Is Nothing Then _
Set rng = Range( _
"L:Q,U:X,Z:AC,AE:AH,AJ:AM,AO:AR,AT:AW,AY:BB,BD:BG," & _
"BI:BL,BN:BQ,BS:BV,BX:CA,CC:CF,CH:CK,CM:CP,CR:CU," & _
"CW:CZ,DB:DE,DG:DJ,DL:DO,DQ:DT,DV:DY,EA:ED,EF:EI," & _
"EK:EN,EP:ES,EU:EX,EZ:FC,FE:FH,FJ:FM,FO:FR")
Application.ScreenUpdating = False
Range("A:R").Select
Selection.AutoFilter
Selection.AutoFilter
Columns("A:FR").EntireColumn.Hidden = False
rng.EntireColumn.Hidden = True
Range("13:13").EntireRow.Hidden = True
Range("A11").Select
Application.ScreenUpdating = True
End Sub
 
Back
Top