| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Increasing Macro Efficieny
There are several Command Buttons on a spreadsheet, most of which hide/unhide
selected columns containing data. One of these hides all the data columns and returns back to the €śMain Index€ť sheet. Because any combination of columns could be shown when this macro is run I thought it best to not to select the entire range and hide, so have specified them individually. Because there are so many ranges, however, this causes the screen to have a minor panic attack. Although the macro runs fine, Im sure there must be a way to simplify it so as to ease these attacks, but am unsure how to go about this. The answer is probably inexcusably obvious, so forgive my stupidity. The code is as follows: Private Sub CommandButton30_Click() If Worksheets("Devices").Columns("I:Q").Hidden = False Then Range("I:Q").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("R:V").Hidden = False Then Range("R:V").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("W:Z").Hidden = False Then Range("W:Z").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AA:AM").Hidden = False Then Range("AA:AM").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AN:AY").Hidden = False Then Range("AN:AY").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AZ:BA").Hidden = False Then Range("AZ:BA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BB:BF").Hidden = False Then Range("BB:BF").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BG:BN").Hidden = False Then Range("BG:BN").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BO:BX").Hidden = False Then Range("BO:BX").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BY:CB").Hidden = False Then Range("BY:CB").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CC:CG").Hidden = False Then Range("CC:CG").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CH:CL").Hidden = False Then Range("CH:CL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CM:CQ").Hidden = False Then Range("CM:CQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CR:CU").Hidden = False Then Range("CR:CU").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CV:DB").Hidden = False Then Range("CV:DB").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DC:DI").Hidden = False Then Range("DC:DI").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DJ:DL").Hidden = False Then Range("DJ:DL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DM:DN").Hidden = False Then Range("DM:DN").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DO:DP").Hidden = False Then Range("DO:DP").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DQ:DZ").Hidden = False Then Range("DQ:DZ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("EA:EG").Hidden = False Then Range("EA:EG").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("EH:EQ").Hidden = False Then Range("EH:EQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("ER:FA").Hidden = False Then Range("ER:FA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FB:FL").Hidden = False Then Range("FB:FL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FM:FP").Hidden = False Then Range("FM:FP").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FQ:GA").Hidden = False Then Range("FQ:GA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GB:GE").Hidden = False Then Range("GB:GE").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GF:GQ").Hidden = False Then Range("GF:GQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GR:GU").Hidden = False Then Range("GR:GU").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("A:H").Hidden = True Then Range("A:H").Select Selection.EntireColumn.Hidden = False Else Range("a1").Select End If Selection.EntireColumn.Hidden = False Range("a1").Select Worksheets("Main Index").Select End Sub Thanks in Advance. Phin |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing Macro Efficieny
As the second line of your macro insert:
Application.Screenupdating = False then as the second to the last line insert: Application.Screenupdating = True "Phin Doyle" wrote: There are several Command Buttons on a spreadsheet, most of which hide/unhide selected columns containing data. One of these hides all the data columns and returns back to the €śMain Index€ť sheet. Because any combination of columns could be shown when this macro is run I thought it best to not to select the entire range and hide, so have specified them individually. Because there are so many ranges, however, this causes the screen to have a minor panic attack. Although the macro runs fine, Im sure there must be a way to simplify it so as to ease these attacks, but am unsure how to go about this. The answer is probably inexcusably obvious, so forgive my stupidity. The code is as follows: Private Sub CommandButton30_Click() If Worksheets("Devices").Columns("I:Q").Hidden = False Then Range("I:Q").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("R:V").Hidden = False Then Range("R:V").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("W:Z").Hidden = False Then Range("W:Z").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AA:AM").Hidden = False Then Range("AA:AM").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AN:AY").Hidden = False Then Range("AN:AY").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AZ:BA").Hidden = False Then Range("AZ:BA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BB:BF").Hidden = False Then Range("BB:BF").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BG:BN").Hidden = False Then Range("BG:BN").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BO:BX").Hidden = False Then Range("BO:BX").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BY:CB").Hidden = False Then Range("BY:CB").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CC:CG").Hidden = False Then Range("CC:CG").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CH:CL").Hidden = False Then Range("CH:CL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CM:CQ").Hidden = False Then Range("CM:CQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CR:CU").Hidden = False Then Range("CR:CU").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CV:DB").Hidden = False Then Range("CV:DB").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DC:DI").Hidden = False Then Range("DC:DI").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DJ:DL").Hidden = False Then Range("DJ:DL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DM:DN").Hidden = False Then Range("DM:DN").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DO:DP").Hidden = False Then Range("DO:DP").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DQ:DZ").Hidden = False Then Range("DQ:DZ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("EA:EG").Hidden = False Then Range("EA:EG").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("EH:EQ").Hidden = False Then Range("EH:EQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("ER:FA").Hidden = False Then Range("ER:FA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FB:FL").Hidden = False Then Range("FB:FL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FM:FP").Hidden = False Then Range("FM:FP").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FQ:GA").Hidden = False Then Range("FQ:GA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GB:GE").Hidden = False Then Range("GB:GE").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GF:GQ").Hidden = False Then Range("GF:GQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GR:GU").Hidden = False Then Range("GR:GU").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("A:H").Hidden = True Then Range("A:H").Select Selection.EntireColumn.Hidden = False Else Range("a1").Select End If Selection.EntireColumn.Hidden = False Range("a1").Select Worksheets("Main Index").Select End Sub Thanks in Advance. Phin |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing Macro Efficieny
Thanks RCW,
So simple yet so effective. That worked brilliantly. Thanks Again "RCW" wrote: As the second line of your macro insert: Application.Screenupdating = False then as the second to the last line insert: Application.Screenupdating = True "Phin Doyle" wrote: There are several Command Buttons on a spreadsheet, most of which hide/unhide selected columns containing data. One of these hides all the data columns and returns back to the €śMain Index€ť sheet. Because any combination of columns could be shown when this macro is run I thought it best to not to select the entire range and hide, so have specified them individually. Because there are so many ranges, however, this causes the screen to have a minor panic attack. Although the macro runs fine, Im sure there must be a way to simplify it so as to ease these attacks, but am unsure how to go about this. The answer is probably inexcusably obvious, so forgive my stupidity. The code is as follows: Private Sub CommandButton30_Click() If Worksheets("Devices").Columns("I:Q").Hidden = False Then Range("I:Q").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("R:V").Hidden = False Then Range("R:V").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("W:Z").Hidden = False Then Range("W:Z").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AA:AM").Hidden = False Then Range("AA:AM").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AN:AY").Hidden = False Then Range("AN:AY").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AZ:BA").Hidden = False Then Range("AZ:BA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BB:BF").Hidden = False Then Range("BB:BF").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BG:BN").Hidden = False Then Range("BG:BN").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BO:BX").Hidden = False Then Range("BO:BX").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BY:CB").Hidden = False Then Range("BY:CB").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CC:CG").Hidden = False Then Range("CC:CG").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CH:CL").Hidden = False Then Range("CH:CL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CM:CQ").Hidden = False Then Range("CM:CQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CR:CU").Hidden = False Then Range("CR:CU").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CV:DB").Hidden = False Then Range("CV:DB").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DC:DI").Hidden = False Then Range("DC:DI").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DJ:DL").Hidden = False Then Range("DJ:DL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DM:DN").Hidden = False Then Range("DM:DN").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DO:DP").Hidden = False Then Range("DO:DP").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DQ:DZ").Hidden = False Then Range("DQ:DZ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("EA:EG").Hidden = False Then Range("EA:EG").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("EH:EQ").Hidden = False Then Range("EH:EQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("ER:FA").Hidden = False Then Range("ER:FA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FB:FL").Hidden = False Then Range("FB:FL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FM:FP").Hidden = False Then Range("FM:FP").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FQ:GA").Hidden = False Then Range("FQ:GA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GB:GE").Hidden = False Then Range("GB:GE").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GF:GQ").Hidden = False Then Range("GF:GQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GR:GU").Hidden = False Then Range("GR:GU").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("A:H").Hidden = True Then Range("A:H").Select Selection.EntireColumn.Hidden = False Else Range("a1").Select End If Selection.EntireColumn.Hidden = False Range("a1").Select Worksheets("Main Index").Select End Sub Thanks in Advance. Phin |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing Macro Efficieny
Private Sub CommandButton30_Click()
With Worksheets("Devices") .Range("I:V").EntireColumn.Hidden = True .Range("BO:GU").EntireColumn.Hidden = True .Range("A:H").EntireColumn.Hidden = False .Rows(1).Hidden = False .Range("a1").Select End With Worksheets("Main Index").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Phin Doyle" wrote in message ... There are several Command Buttons on a spreadsheet, most of which hide/unhide selected columns containing data. One of these hides all the data columns and returns back to the "Main Index" sheet. Because any combination of columns could be shown when this macro is run I thought it best to not to select the entire range and hide, so have specified them individually. Because there are so many ranges, however, this causes the screen to have a minor panic attack. Although the macro runs fine, I'm sure there must be a way to simplify it so as to ease these attacks, but am unsure how to go about this. The answer is probably inexcusably obvious, so forgive my stupidity. The code is as follows: Private Sub CommandButton30_Click() If Worksheets("Devices").Columns("I:Q").Hidden = False Then Range("I:Q").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("R:V").Hidden = False Then Range("R:V").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("W:Z").Hidden = False Then Range("W:Z").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AA:AM").Hidden = False Then Range("AA:AM").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AN:AY").Hidden = False Then Range("AN:AY").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AZ:BA").Hidden = False Then Range("AZ:BA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BB:BF").Hidden = False Then Range("BB:BF").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BG:BN").Hidden = False Then Range("BG:BN").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BO:BX").Hidden = False Then Range("BO:BX").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BY:CB").Hidden = False Then Range("BY:CB").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CC:CG").Hidden = False Then Range("CC:CG").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CH:CL").Hidden = False Then Range("CH:CL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CM:CQ").Hidden = False Then Range("CM:CQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CR:CU").Hidden = False Then Range("CR:CU").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CV:DB").Hidden = False Then Range("CV:DB").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DC:DI").Hidden = False Then Range("DC:DI").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DJ:DL").Hidden = False Then Range("DJ:DL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DM:DN").Hidden = False Then Range("DM:DN").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DO:DP").Hidden = False Then Range("DO:DP").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DQ:DZ").Hidden = False Then Range("DQ:DZ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("EA:EG").Hidden = False Then Range("EA:EG").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("EH:EQ").Hidden = False Then Range("EH:EQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("ER:FA").Hidden = False Then Range("ER:FA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FB:FL").Hidden = False Then Range("FB:FL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FM:FP").Hidden = False Then Range("FM:FP").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FQ:GA").Hidden = False Then Range("FQ:GA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GB:GE").Hidden = False Then Range("GB:GE").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GF:GQ").Hidden = False Then Range("GF:GQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GR:GU").Hidden = False Then Range("GR:GU").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("A:H").Hidden = True Then Range("A:H").Select Selection.EntireColumn.Hidden = False Else Range("a1").Select End If Selection.EntireColumn.Hidden = False Range("a1").Select Worksheets("Main Index").Select End Sub Thanks in Advance. Phin |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Increasing Macro Efficieny
Bob,
That would've saved me an inordinate amount of time had I asked sooner. I know for next time. Thanks "Bob Phillips" wrote: Private Sub CommandButton30_Click() With Worksheets("Devices") .Range("I:V").EntireColumn.Hidden = True .Range("BO:GU").EntireColumn.Hidden = True .Range("A:H").EntireColumn.Hidden = False .Rows(1).Hidden = False .Range("a1").Select End With Worksheets("Main Index").Select End Sub -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Phin Doyle" wrote in message ... There are several Command Buttons on a spreadsheet, most of which hide/unhide selected columns containing data. One of these hides all the data columns and returns back to the "Main Index" sheet. Because any combination of columns could be shown when this macro is run I thought it best to not to select the entire range and hide, so have specified them individually. Because there are so many ranges, however, this causes the screen to have a minor panic attack. Although the macro runs fine, I'm sure there must be a way to simplify it so as to ease these attacks, but am unsure how to go about this. The answer is probably inexcusably obvious, so forgive my stupidity. The code is as follows: Private Sub CommandButton30_Click() If Worksheets("Devices").Columns("I:Q").Hidden = False Then Range("I:Q").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("R:V").Hidden = False Then Range("R:V").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("W:Z").Hidden = False Then Range("W:Z").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AA:AM").Hidden = False Then Range("AA:AM").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AN:AY").Hidden = False Then Range("AN:AY").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("AZ:BA").Hidden = False Then Range("AZ:BA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BB:BF").Hidden = False Then Range("BB:BF").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BG:BN").Hidden = False Then Range("BG:BN").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BO:BX").Hidden = False Then Range("BO:BX").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("BY:CB").Hidden = False Then Range("BY:CB").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CC:CG").Hidden = False Then Range("CC:CG").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CH:CL").Hidden = False Then Range("CH:CL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CM:CQ").Hidden = False Then Range("CM:CQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CR:CU").Hidden = False Then Range("CR:CU").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("CV:DB").Hidden = False Then Range("CV:DB").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DC:DI").Hidden = False Then Range("DC:DI").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DJ:DL").Hidden = False Then Range("DJ:DL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DM:DN").Hidden = False Then Range("DM:DN").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DO:DP").Hidden = False Then Range("DO:DP").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("DQ:DZ").Hidden = False Then Range("DQ:DZ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("EA:EG").Hidden = False Then Range("EA:EG").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("EH:EQ").Hidden = False Then Range("EH:EQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("ER:FA").Hidden = False Then Range("ER:FA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FB:FL").Hidden = False Then Range("FB:FL").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FM:FP").Hidden = False Then Range("FM:FP").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("FQ:GA").Hidden = False Then Range("FQ:GA").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GB:GE").Hidden = False Then Range("GB:GE").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GF:GQ").Hidden = False Then Range("GF:GQ").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("GR:GU").Hidden = False Then Range("GR:GU").Select Selection.EntireColumn.Hidden = True Else Range("a1").Select End If Selection.EntireColumn.Hidden = True If Worksheets("Devices").Columns("A:H").Hidden = True Then Range("A:H").Select Selection.EntireColumn.Hidden = False Else Range("a1").Select End If Selection.EntireColumn.Hidden = False Range("a1").Select Worksheets("Main Index").Select End Sub Thanks in Advance. Phin |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| how do I email amacro? | Excel Worksheet Functions | |||
| error when running cut & paste macro | Excel Worksheet Functions | |||
| Search, Copy, Paste Macro in Excel | Excel Worksheet Functions | |||
| Closing File Error | Excel Discussion (Misc queries) | |||
| Highlight Range - wrong macro, please edit. | Excel Worksheet Functions | |||
Linear Mode
