Macro Covering Many Columns, why has to be UNION

L

Lor

I am not understanding why this must be a UNION vs just a list of the
columns I want to change the format to make "Currency"

Why can't I just list the columns ~ if is due to limitation of # of
columns, then does anyone know how many columns can be listed.


Union(Range( _

"CG:CG,CI:CI,CK:CK,CM:CM,CO:CO,CQ:CQ,CS:CS,CU:CU,CW:CW,CY:CY,DA:DA,DC:DC,DE:DE,DG:DG,DI:DI,DK:DK,DM:DM,DO:DO,DQ:DQ,DS:DS,DU:DU,DW:DW,DY:DY,J:O,W:W,Y:Y,AA:AA,AC:AC,AE:AE,AG:AG,AI:AI,AK:AK"
_
), Range( _

"AM:AM,AO:AO,AQ:AQ,AS:AS,AU:AU,AW:AW,AY:AY,BA:BA,BC:BC,BE:BE,BG:BG,BI:BI,BK:BK,BM:BM,BO:BO,BQ:BQ,BS:BS,BU:BU,BW:BW,BY:BY,CA:CA,CC:CC,CE:CE"
_
)).Select
Union(Range( _

"CG:CG,CI:CI,CK:CK,CM:CM,CO:CO,CQ:CQ,CS:CS,CU:CU,CW:CW,CY:CY,DA:DA,DC:DC,DE:DE,DG:DG,DI:DI,DK:DK,DM:DM,DO:DO,DQ:DQ,DS:DS,DU:DU,DW:DW,DY:DY,EA:EA,EC:EC,EE:EE,EG:EG,EI:EI,EK:EK,EM:EM,EO:EO,EQ:EQ"
_
), Range( _

"ES:ES,EU:EU,J:O,W:W,Y:Y,AA:AA,AC:AC,AE:AE,AG:AG,AI:AI,AK:AK,AM:AM,AO:AO,AQ:AQ,AS:AS,AU:AU,AW:AW,AY:AY,BA:BA,BC:BC,BE:BE,BG:BG,BI:BI,BK:BK,BM:BM,BO:BO,BQ:BQ,BS:BS,BU:BU,BW:BW,BY:BY,CA:CA"
_
), Range("CC:CC,CE:CE")).Select
Selection.NumberFormat = "$#,##0"
 
C

Charles C

Hi,

I hope I understand what you want.

Here 2 methods:

1-Sub ArrayCol: First create an arrays, load the list, then loo
through the list.

2-Sub ColJumpOne: Looks like your want every second columns to b
affected. You can use a simple For Next step 2 then start on the colu
(CG = 85)



Code
-------------------
Sub ArrayCol()
Dim x As Integer
Dim c As Variant

c = Array("CG:CG", "CI:CI", "CK:CK", "CM:CM", "CO:CO", "CQ:CQ", "CS:CS", "CU:CU")

For x = 0 To UBound(c)
Range(c(x)).NumberFormat = "$#,##0"
Next x
End Sub

Sub ColJumpOne()
Dim x As Integer

For x = 85 To 260 Step 2
Columns(x).NumberFormat = "$#,##0"
Next x
End Su
 
D

Dana DeLouis

I am not understanding why this must be a UNION vs just a list of the
columns I want to change the format to make "Currency"


Hi. I can't tell because if works fine with Excel 2007.
There may be a limit of 30 arguments in earlier versions, although I am
not sure of that.

Would this general idea work?

Sub Demo()
Dim C As Long '(C)olumn
Dim s, e 'Start & End
Const fm As String = "$#,##0"

[J:O].NumberFormat = fm
With [W:DY]
s = .Columns(1).Column
e = s + .Columns.Count - 1
End With

For C = s To e Step 2
Columns(C).NumberFormat = fm
Next C
End Sub

= = =
HTH
Dana DeLouis
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top