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
|