|
Macro too slow...how to optimize
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:B L ,BN:BQ,BS:BV,BX:CA,CC:CF,CH:CK,CM:CP,CR:CU,CW:CZ,D B: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 |
Macro too slow...how to optimize
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 In article , "Paul" wrote: 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:B L ,BN:BQ,BS:BV,BX:CA,CC:CF,CH:CK,CM:CP,CR:CU,CW:CZ,D B: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 |
| All times are GMT +1. The time now is 06:01 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com