Long Strings
 

I don't think you need such long strings ...

One (shorter) way:

Set NewRange = _
Range("A1:F4,A22:F25,A43:F46,A64:F67,A85:F88,A106: F109,A127:F130")
Set NewRange = Union(NewRange, _
Range("G1:L4,G22:L25,G43:L46,G64:L67,G85:L88,G106: L109,G127:L130"))
Set NewRange = Union(NewRange, _
Range("M1:R4,M22:R25,M43:R46,M64:R67,M85:R88,M106: R109,M127:R130"))
Set NewRange = Union(NewRange, _
Range("S1:X4,S22:X25,S43:X46,S64:X67,S85:X88,S106: X109,S127:X130"))

But even shorter:

Set NewRange = _
Range("A1:X4,A22:X25,A43:X46,A64:X67,A85:X88,A106: X109,A127:X130")

With NewRange
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Regards

Trevor


"Donna C" wrote in message
...
I am pretty new to this VB programming stuff and I am having some
problems.I
am trying to merge cells across a wide selection of cells on a work sheet.
It
worked fine when on a single line (column A:F for example) however the
scrolling issue came in.I tried to concatenate and combine things however
something is amiss. Its probably something simple but I can't see it.Any
suggestions would be great.Heres what I've got.
Range("A1:F1,A2:F2,A3:F3,A4:F4,A22:F22,A23:F23,A24 :F24,A25:F25,A43:F43" &
_
"
A44:F44,A45:F45,A46:F46,A64:F64,A65:F65,A66:F66,A6 7:F67,A85:F85,A86:F86" &
_
" A87:F87,A88:F88,A106:F106,A107:F107,A108:F108,A109 :F109" & _
" A127:F127,A128:F128,A129:F129,A130:F130" & _
" G1:L1,G2:L2,G3:L3,G4:L4,G22:L22,G23:L23,G24:L24,G2 5:L25" & _
" G43:L43,G44:L44,G45:L45,G46:L46,G64:L64,G65:L65,G6 6:L66" & _
" G67:L67,G85:L85,G86:L86,G87:L87,G88:L88,G106:L106" & _
"
G107:L107,G108:L108,G109:L109,G127:L127,G128:L128, G129:L129,G130:L130" & _
" M1:R1,M2:R2,M3:R3,M4:R4,M22:R22,M23:R23,M24:R24,M2 5:R25,M43:R43"
& _
" M44:R44,M45:R45,M46:R46,M64:R64,M65:R65,M66:R66,M6 7:R67,M85:R85"
& _
" M86:R86,M87:R87,M88:R88,M106:R106,M107:R107,M108:R 108,M109:R109"
& _
" M127:R127,M128:R128,M129:R129,M130:R130" & _
" S1:X1,S2:X2,S3:X3,S4:X4,S22:X22,S23:X23,S24:X24,S2 5:X25" & _
" S43:X43,S44:X44,S45:X45,S46:X46,S64:X64,S65:X65,S6 6:X66" & _
" S67:X67,S85:X85,S86:X86,S87:X87,S88:X88,S106:X106, S107:X107" & _
"
S108:X108,S109:X109,S127:X127,S128:X128,S129:X129, S130:X130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Thanks in advance
Donna C.

Long Strings
 

Trevor - I'm guessing the OP wants to merge a large number of areas. So,
although the "shorter" and "even shorter" ways would be fine applying
formats they'll merge a small number of large areas.

Donna - The string method will keep your multiple areas intact for merging
but there's an address string limit of 255. You can probably do what you
want in about 4 shots with strings within this limit. Perhaps make an array
of strings and loop

dim s(1 to 4) as string

s(1) = "A87:F87,A88:F88,A106:F106,A107:F107,A108:F108,A10 9:F109," & _
"A127:F127,A128:F128,A129:F129,A130:F130," & _ etc
s(2) = etc

(dont forget the commas at the end of all lines except the last line

for i = 1 to 4
with range(s(i))
.mergecells = true
.other formats
end with
next

Regards,
Peter T

PS afraid I'm too lazy to test it!


"Trevor Shuttleworth" wrote in message
...
I don't think you need such long strings ...

One (shorter) way:

Set NewRange = _
Range("A1:F4,A22:F25,A43:F46,A64:F67,A85:F88,A106: F109,A127:F130")
Set NewRange = Union(NewRange, _
Range("G1:L4,G22:L25,G43:L46,G64:L67,G85:L88,G106: L109,G127:L130"))
Set NewRange = Union(NewRange, _
Range("M1:R4,M22:R25,M43:R46,M64:R67,M85:R88,M106: R109,M127:R130"))
Set NewRange = Union(NewRange, _
Range("S1:X4,S22:X25,S43:X46,S64:X67,S85:X88,S106: X109,S127:X130"))

But even shorter:

Set NewRange = _
Range("A1:X4,A22:X25,A43:X46,A64:X67,A85:X88,A106: X109,A127:X130")

With NewRange
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Regards

Trevor


"Donna C" wrote in message
...
I am pretty new to this VB programming stuff and I am having some
problems.I
am trying to merge cells across a wide selection of cells on a work
sheet.
It
worked fine when on a single line (column A:F for example) however the
scrolling issue came in.I tried to concatenate and combine things
however
something is amiss. Its probably something simple but I can't see it.Any
suggestions would be great.Heres what I've got.
Range("A1:F1,A2:F2,A3:F3,A4:F4,A22:F22,A23:F23,A24 :F24,A25:F25,A43:F43"
&
_
"
A44:F44,A45:F45,A46:F46,A64:F64,A65:F65,A66:F66,A6 7:F67,A85:F85,A86:F86"
&
_
" A87:F87,A88:F88,A106:F106,A107:F107,A108:F108,A109 :F109" & _
" A127:F127,A128:F128,A129:F129,A130:F130" & _
" G1:L1,G2:L2,G3:L3,G4:L4,G22:L22,G23:L23,G24:L24,G2 5:L25" & _
" G43:L43,G44:L44,G45:L45,G46:L46,G64:L64,G65:L65,G6 6:L66" & _
" G67:L67,G85:L85,G86:L86,G87:L87,G88:L88,G106:L106" & _
"
G107:L107,G108:L108,G109:L109,G127:L127,G128:L128, G129:L129,G130:L130" &
_
"
M1:R1,M2:R2,M3:R3,M4:R4,M22:R22,M23:R23,M24:R24,M2 5:R25,M43:R43"
& _
"
M44:R44,M45:R45,M46:R46,M64:R64,M65:R65,M66:R66,M6 7:R67,M85:R85"
& _
"
M86:R86,M87:R87,M88:R88,M106:R106,M107:R107,M108:R 108,M109:R109"
& _
" M127:R127,M128:R128,M129:R129,M130:R130" & _
" S1:X1,S2:X2,S3:X3,S4:X4,S22:X22,S23:X23,S24:X24,S2 5:X25" & _
" S43:X43,S44:X44,S45:X45,S46:X46,S64:X64,S65:X65,S6 6:X66" & _
" S67:X67,S85:X85,S86:X86,S87:X87,S88:X88,S106:X106, S107:X107" &
_
"
S108:X108,S109:X109,S127:X127,S128:X128,S129:X129, S130:X130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Thanks in advance
Donna C.

Long Strings
 

Trevor,
Thank you for your suggestion.
I ran the sub, it meges the desired rows the only problem is it merges all
cells on those lines from A-X. I just needed select columns and rows for a
group of forms.Any other suggestions would be great.
Thanks
Donna C.

"Trevor Shuttleworth" wrote:

I don't think you need such long strings ...

One (shorter) way:

Set NewRange = _
Range("A1:F4,A22:F25,A43:F46,A64:F67,A85:F88,A106: F109,A127:F130")
Set NewRange = Union(NewRange, _
Range("G1:L4,G22:L25,G43:L46,G64:L67,G85:L88,G106: L109,G127:L130"))
Set NewRange = Union(NewRange, _
Range("M1:R4,M22:R25,M43:R46,M64:R67,M85:R88,M106: R109,M127:R130"))
Set NewRange = Union(NewRange, _
Range("S1:X4,S22:X25,S43:X46,S64:X67,S85:X88,S106: X109,S127:X130"))

But even shorter:

Set NewRange = _
Range("A1:X4,A22:X25,A43:X46,A64:X67,A85:X88,A106: X109,A127:X130")

With NewRange
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Regards

Trevor


"Donna C" wrote in message
...
I am pretty new to this VB programming stuff and I am having some
problems.I
am trying to merge cells across a wide selection of cells on a work sheet.
It
worked fine when on a single line (column A:F for example) however the
scrolling issue came in.I tried to concatenate and combine things however
something is amiss. Its probably something simple but I can't see it.Any
suggestions would be great.Heres what I've got.
Range("A1:F1,A2:F2,A3:F3,A4:F4,A22:F22,A23:F23,A24 :F24,A25:F25,A43:F43" &
_
"
A44:F44,A45:F45,A46:F46,A64:F64,A65:F65,A66:F66,A6 7:F67,A85:F85,A86:F86" &
_
" A87:F87,A88:F88,A106:F106,A107:F107,A108:F108,A109 :F109" & _
" A127:F127,A128:F128,A129:F129,A130:F130" & _
" G1:L1,G2:L2,G3:L3,G4:L4,G22:L22,G23:L23,G24:L24,G2 5:L25" & _
" G43:L43,G44:L44,G45:L45,G46:L46,G64:L64,G65:L65,G6 6:L66" & _
" G67:L67,G85:L85,G86:L86,G87:L87,G88:L88,G106:L106" & _
"
G107:L107,G108:L108,G109:L109,G127:L127,G128:L128, G129:L129,G130:L130" & _
" M1:R1,M2:R2,M3:R3,M4:R4,M22:R22,M23:R23,M24:R24,M2 5:R25,M43:R43"
& _
" M44:R44,M45:R45,M46:R46,M64:R64,M65:R65,M66:R66,M6 7:R67,M85:R85"
& _
" M86:R86,M87:R87,M88:R88,M106:R106,M107:R107,M108:R 108,M109:R109"
& _
" M127:R127,M128:R128,M129:R129,M130:R130" & _
" S1:X1,S2:X2,S3:X3,S4:X4,S22:X22,S23:X23,S24:X24,S2 5:X25" & _
" S43:X43,S44:X44,S45:X45,S46:X46,S64:X64,S65:X65,S6 6:X66" & _
" S67:X67,S85:X85,S86:X86,S87:X87,S88:X88,S106:X106, S107:X107" & _
"
S108:X108,S109:X109,S127:X127,S128:X128,S129:X129, S130:X130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Thanks in advance
Donna C.