Select range from a varaible

[Excel User]

Hi,

I'm trying to set a range that is hidden from an array, but cannot seem to
get it working, also is there a better way to set the range as you can see
from below after rng1 is set, rng2 is one column right from each column in
rng1, rng3 is one column right from each column in rng2 etc

private function hidecols(who)

Dim RngToHide As Range

RngToHide = who

rng1= "BW:BW,CC:CC,CI:CI,CO:CO,CU:CU,DAA,DGG,DMM,DSS,DYY"
rng2 =
"BX:BX,CD:CD,CJ:CJ,CP:CP,CV:CV,DBB,DHH,DNN,DTT,DZZ,EF:EF"
rng3 =
"BY:BY,CE:CE,CK:CK,CQ:CQ,CW:CW,DCC,DII,DOO,DUU,DAA,EG:EG"
rng4 =
"BZ:BZ,CF:CF,CL:CL,CR:CR,CX:CX,DDD,DJJ,DPP,DVV,DBB,EH:EH,EN:EN"
rng5 =
"BA:BA,CG:CG,CM:CM,CS:CS,CY:CV,DEE,DKK,DQQ,DWW,DCC,EI:EI,EO:EO"


Range(RngToHide).Select
Selection.EntireColumn.Hidden = True
End Sub

 

[Joel]

Try this

Sub test()
Call hidecols("rng1")
End Sub
Private Function hidecols(who)

Dim RngToHide As Range

Select Case who
Case "rng1":
rng = "BW:BW,CC:CC,CI:CI,CO:CO,CU:CU,DAA,DGG,DMM,DSS,DYY"
Case "rng2":
rng = "BX:BX,CD:CD,CJ:CJ,CP:CP,CV:CV,DBB,DHH,DNN,DTT,DZZ,EF:EF"
Case "rng3":
rng = "BY:BY,CE:CE,CK:CK,CQ:CQ,CW:CW,DCC,DII,DOO,DUU,DAA,EG:EG"
Case "rng4":
rng =
"BZ:BZ,CF:CF,CL:CL,CR:CR,CX:CX,DDD,DJJ,DPP,DVV,DBB,EH:EH,EN:EN"
Case "rng5":
rng =
"BA:BA,CG:CG,CM:CM,CS:CS,CY:CV,DEE,DKK,DQQ,DWW,DCC,EI:EI,EO:EO"
End Select

Range(rng).Select
Selection.EntireColumn.Hidden = True
End Function

 

[Excel User]

Joel,

Thanks for your code, I think I have found a bug, if I select the same range
in Excel and hide it works fine, however if I use the code to hide the
columns any merged cells which span the columns are also hidden - am I doing
something wrong?

i.e. cell bw5 to eo5 are a merged cell, rng1 includes bw if I try hiding the
column via the code all columns are hidden, but if you try doing this in
Excel it works - strange?

Thanks

 

[Rick Rothstein]

This is a slightly different approach, but it seems to work with merged
cells. In the subroutine below, you would pass in a "group" number instead
of a range name. For example, if you wanted to hide the columns associated
with "rng3", you would just pass in the 3. For example...

Sub Test()
HideColumnGroup 3
End Sub

Here is the code for the HideColumnGroup subroutine...

Sub HideColumnGroup(Who As Long)
Range("BW:ED").EntireColumn.Hidden = False
Range("BW:BW,CC:CC,CI:CI,CO:CO,CU:CU,DAA,DGG,DMM,DSS,DYY"). _
EntireColumn.Offset(, Who - 1).Hidden = True
End Sub

Note that the first statement of the subroutine unhides **all** hidden rows
within the grouping area (cell range BW:ED) before it hides the requested
group of columns. If you don't want previously hidden columns to become
unhidden, the just remove that first statement.

 

[Joel]

Your original posted code are assigning the ranges to variables rng1 - 5 but
these ranges weren't being used. You also passed a vairable who that wasn't
being used. My code just fixed these problems in your original code.

 

[Excel User]

Rick that's cool - thanks again !

This is a slightly different approach, but it seems to work with merged
cells. In the subroutine below, you would pass in a "group" number instead
of a range name. For example, if you wanted to hide the columns associated
with "rng3", you would just pass in the 3. For example...

Sub Test()
HideColumnGroup 3
End Sub

Here is the code for the HideColumnGroup subroutine...

Sub HideColumnGroup(Who As Long)
Range("BW:ED").EntireColumn.Hidden = False
Range("BW:BW,CC:CC,CI:CI,CO:CO,CU:CU,DAA,DGG,DMM,DSS,DYY"). _
EntireColumn.Offset(, Who - 1).Hidden = True
End Sub

Note that the first statement of the subroutine unhides **all** hidden
rows within the grouping area (cell range BW:ED) before it hides the
requested group of columns. If you don't want previously hidden columns to
become unhidden, the just remove that first statement.