ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Long Strings (https://www.excelbanter.com/excel-programming/379362-re-long-strings.html)

Trevor Shuttleworth
Long Strings
 
Donna

I know you now have the answer you were looking for. Just wanted to point
out that you don't need to select a range in order to format it. For
example:

Range("A:A,G:G,M:M,S:S").ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").ColumnWid th = 2
Range("E:E,K:K,Q:Q,W:W").ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").ColumnWidth = 14

has the same effect as:

Range("A:A,G:G,M:M,S:S").Select
Selection.ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").Select
Selection.ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").Select
Selection.ColumnWidth = 2
Range("E:E,K:K,Q:Q,W:W").Select
Selection.ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").Select
Selection.ColumnWidth = 14

It's obviously less to code but it's also more efficient and quicker than
the select/selection approach. It doesn't move the selection either so you
don't need to reposition afterwards.

You can also say "With Range(....)" rather than "Range(....).Select" and
"With Selection"

In fact, the whole thing can be reduced to:

Sub Form1()
'
' Form1 Macro
' Macro recorded 12/9/2006 by Donna Casty
' THIS FORMATS MY ROW WIDTHS
' THEN MERGES THE CELLS ACROSS 6 COLUMNS
' AND DOWN 4 ROWS
' I AM TRYING TO CREATE DUPLICATE FORMS ON ONE PAGE.
'
Range("A:A,G:G,M:M,S:S").ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").ColumnWid th = 2
Range("E:E,K:K,Q:Q,W:W").ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").ColumnWidth = 14

With
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:F 64,A65:F65,A66:F66,A67:F67,A85:F85,A86:F86,A87:F87 ,A88:F88,A106:F106,A107:F107,A108:F108,A109:F109,A 127:F127,A128:F128,A129:F129,A130:F130")
.MergeCells = True
End With
With
Range("G1:L1,G2:L2,G3:L3,G4:L4,G22:L22,G23:L23,G24 :L24,G25:L25,G43:L43,G44:L44,G45:L45,G46:L46,G64:L 64,G65:L65,G66:L66,G67:L67,G85:L85,G86:L86,G87:L87 ,G88:L88,G106:L106,G107:L107,G108:L108,G109:L109,G 127:L127,G128:L128,G129:L129,G130:L130")
.MergeCells = True
End With
With
Range("M1:R1,M2:R2,M3:R3,M4:R4,M22:R22,M23:R23,M24 :R24,M25:R25,M43:R43,M44:R44,M45:R45,M46:R46,M64:R 64,M65:R65,M66:R66,M67:R67,M85:R85,M86:R86,M87:R87 ,M88:R88,M106:R106,M107:R107,M108:R108,M109:R109,M 127:R127,M128:R128,M129:R129,M130:R130")
.MergeCells = True
End With
With
Range("S1:X1,S2:X2,S3:X3,S4:X4,S22:X22,S23:X23,S24 :X24,S25:X25,S43:X43,S44:X44,S45:X45,S46:X46,S64:X 64,S65:X65,S66:X66,S67:X67,S85:X85,S86:X86,S87:X87 ,S88:X88,S106:X106,S107:X107,S108:X108,S109:X109,S 127:X127,S128:X128,S129:X129,S130:X130")
.MergeCells = True
End With

End Sub

This assumes that the other format effects are the defaults and don't need
to be changed.

Regards

Trevor


"Donna C" wrote in message
...
Heres the whole thing so its not so hard to explain, but its kinda
windy,sorry.Try it and see what it does while I try some of the other
suggestions. Thank you all for helping.
Donna C.
Sub Form1()
'
' Form1 Macro
' Macro recorded 12/9/2006 by Donna Casty
'THIS FORMATS MY ROW WIDTHS
'THEN MERGES THE CELLS ACROSS 6 COLUMNS
'AND DOWN 4 ROWS
'I AM TRYING TO CREATE DUPLICATE FORMS ON ONE PAGE.
'
Range("A:A,G:G,M:M,S:S").Select
Selection.ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").Select
Selection.ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").Select
Selection.ColumnWidth = 2
Range("E:E,K:K,Q:Q,W:W").Select
Selection.ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").Select
Selection.ColumnWidth = 14


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:F 64,A65:F65,A66:F66,A67:F67,A85:F85,A86:F86,A87:F87 ,A88:F88,A106:F106,A107:F107,A108:F108,A109:F109,A 127:F127,A128:F128,A129:F129,A130:F130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With


Range("G1:L1,G2:L2,G3:L3,G4:L4,G22:L22,G23:L23,G24 :L24,G25:L25,G43:L43,G44:L44,G45:L45,G46:L46,G64:L 64,G65:L65,G66:L66,G67:L67,G85:L85,G86:L86,G87:L87 ,G88:L88,G106:L106,G107:L107,G108:L108,G109:L109,G 127:L127,G128:L128,G129:L129,G130:L130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range("M1:R1,M2:R2,M3:R3,M4:R4,M22:R22,M23:R23,M24 :R24,M25:R25,M43:R43,M44:R44,M45:R45,M46:R46,M64:R 64,M65:R65,M66:R66,M67:R67,M85:R85,M86:R86,M87:R87 ,M88:R88,M106:R106,M107:R107,M108:R108,M109:R109,M 127:R127,M128:R128,M129:R129,M130:R130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range("S1:X1,S2:X2,S3:X3,S4:X4,S22:X22,S23:X23,S24 :X24,S25:X25,S43:X43,S44:X44,S45:X45,S46:X46,S64:X 64,S65:X65,S66:X66,S67:X67,S85:X85,S86:X86,S87:X87 ,S88:X88,S106:X106,S107:X107,S108:X108,S109:X109,S 127: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

End Sub

"Donna C" wrote:

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.



Trevor Shuttleworth
Long Strings
 
Donna

I thought I'd already re-written the code ;-)

The With Range("....") needs to be on one line; it word wrapped in
the post so that may be the problem. You'd see the With highlighted
in red but everything else would look OK. If you try to run it you'd get a
Compile error: Syntax error.

If it's not that, please post your code.

Regards

Trevor


"Donna C" wrote in message
...
Trevor,
Thank you for your time and suggestions.
I tried rewriting like you suggested using
the "With Range" suggestion,But that part seems
to lock up.
Donna C.

"Trevor Shuttleworth" wrote:

Donna

I know you now have the answer you were looking for. Just wanted to
point
out that you don't need to select a range in order to format it. For
example:

Range("A:A,G:G,M:M,S:S").ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").ColumnWid th = 2
Range("E:E,K:K,Q:Q,W:W").ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").ColumnWidth = 14

has the same effect as:

Range("A:A,G:G,M:M,S:S").Select
Selection.ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").Select
Selection.ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").Select
Selection.ColumnWidth = 2
Range("E:E,K:K,Q:Q,W:W").Select
Selection.ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").Select
Selection.ColumnWidth = 14

It's obviously less to code but it's also more efficient and quicker than
the select/selection approach. It doesn't move the selection either so
you
don't need to reposition afterwards.

You can also say "With Range(....)" rather than "Range(....).Select" and
"With Selection"

In fact, the whole thing can be reduced to:

Sub Form1()
'
' Form1 Macro
' Macro recorded 12/9/2006 by Donna Casty
' THIS FORMATS MY ROW WIDTHS
' THEN MERGES THE CELLS ACROSS 6 COLUMNS
' AND DOWN 4 ROWS
' I AM TRYING TO CREATE DUPLICATE FORMS ON ONE PAGE.
'
Range("A:A,G:G,M:M,S:S").ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").ColumnWid th = 2
Range("E:E,K:K,Q:Q,W:W").ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").ColumnWidth = 14

With
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:F 64,A65:F65,A66:F66,A67:F67,A85:F85,A86:F86,A87:F87 ,A88:F88,A106:F106,A107:F107,A108:F108,A109:F109,A 127:F127,A128:F128,A129:F129,A130:F130")
.MergeCells = True
End With
With
Range("G1:L1,G2:L2,G3:L3,G4:L4,G22:L22,G23:L23,G24 :L24,G25:L25,G43:L43,G44:L44,G45:L45,G46:L46,G64:L 64,G65:L65,G66:L66,G67:L67,G85:L85,G86:L86,G87:L87 ,G88:L88,G106:L106,G107:L107,G108:L108,G109:L109,G 127:L127,G128:L128,G129:L129,G130:L130")
.MergeCells = True
End With
With
Range("M1:R1,M2:R2,M3:R3,M4:R4,M22:R22,M23:R23,M24 :R24,M25:R25,M43:R43,M44:R44,M45:R45,M46:R46,M64:R 64,M65:R65,M66:R66,M67:R67,M85:R85,M86:R86,M87:R87 ,M88:R88,M106:R106,M107:R107,M108:R108,M109:R109,M 127:R127,M128:R128,M129:R129,M130:R130")
.MergeCells = True
End With
With
Range("S1:X1,S2:X2,S3:X3,S4:X4,S22:X22,S23:X23,S24 :X24,S25:X25,S43:X43,S44:X44,S45:X45,S46:X46,S64:X 64,S65:X65,S66:X66,S67:X67,S85:X85,S86:X86,S87:X87 ,S88:X88,S106:X106,S107:X107,S108:X108,S109:X109,S 127:X127,S128:X128,S129:X129,S130:X130")
.MergeCells = True
End With

End Sub

This assumes that the other format effects are the defaults and don't
need
to be changed.

Regards

Trevor


"Donna C" wrote in message
...
Heres the whole thing so its not so hard to explain, but its kinda
windy,sorry.Try it and see what it does while I try some of the other
suggestions. Thank you all for helping.
Donna C.
Sub Form1()
'
' Form1 Macro
' Macro recorded 12/9/2006 by Donna Casty
'THIS FORMATS MY ROW WIDTHS
'THEN MERGES THE CELLS ACROSS 6 COLUMNS
'AND DOWN 4 ROWS
'I AM TRYING TO CREATE DUPLICATE FORMS ON ONE PAGE.
'
Range("A:A,G:G,M:M,S:S").Select
Selection.ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").Select
Selection.ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").Select
Selection.ColumnWidth = 2
Range("E:E,K:K,Q:Q,W:W").Select
Selection.ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").Select
Selection.ColumnWidth = 14


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:F 64,A65:F65,A66:F66,A67:F67,A85:F85,A86:F86,A87:F87 ,A88:F88,A106:F106,A107:F107,A108:F108,A109:F109,A 127:F127,A128:F128,A129:F129,A130:F130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With


Range("G1:L1,G2:L2,G3:L3,G4:L4,G22:L22,G23:L23,G24 :L24,G25:L25,G43:L43,G44:L44,G45:L45,G46:L46,G64:L 64,G65:L65,G66:L66,G67:L67,G85:L85,G86:L86,G87:L87 ,G88:L88,G106:L106,G107:L107,G108:L108,G109:L109,G 127:L127,G128:L128,G129:L129,G130:L130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range("M1:R1,M2:R2,M3:R3,M4:R4,M22:R22,M23:R23,M24 :R24,M25:R25,M43:R43,M44:R44,M45:R45,M46:R46,M64:R 64,M65:R65,M66:R66,M67:R67,M85:R85,M86:R86,M87:R87 ,M88:R88,M106:R106,M107:R107,M108:R108,M109:R109,M 127:R127,M128:R128,M129:R129,M130:R130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range("S1:X1,S2:X2,S3:X3,S4:X4,S22:X22,S23:X23,S24 :X24,S25:X25,S43:X43,S44:X44,S45:X45,S46:X46,S64:X 64,S65:X65,S66:X66,S67:X67,S85:X85,S86:X86,S87:X87 ,S88:X88,S106:X106,S107:X107,S108:X108,S109:X109,S 127: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

End Sub

"Donna C" wrote:

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.

Trevor Shuttleworth
Long Strings
 
My pleasure. Thanks for letting me know how you got on. I'm glad I could
help ... it's what the NG is about.

Regards

Trevor


"Donna C" wrote in message
...
Yes you did,and thank you.
It was to many cut and pastes that caused the problem.
I just rewrote it. Thanks again
Donna C.

"Trevor Shuttleworth" wrote:

Donna

I thought I'd already re-written the code ;-)

The With Range("....") needs to be on one line; it word wrapped
in
the post so that may be the problem. You'd see the With
highlighted
in red but everything else would look OK. If you try to run it you'd get
a
Compile error: Syntax error.

If it's not that, please post your code.

Regards

Trevor


"Donna C" wrote in message
...
Trevor,
Thank you for your time and suggestions.
I tried rewriting like you suggested using
the "With Range" suggestion,But that part seems
to lock up.
Donna C.

"Trevor Shuttleworth" wrote:

Donna

I know you now have the answer you were looking for. Just wanted to
point
out that you don't need to select a range in order to format it. For
example:

Range("A:A,G:G,M:M,S:S").ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").ColumnWid th = 2
Range("E:E,K:K,Q:Q,W:W").ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").ColumnWidth = 14

has the same effect as:

Range("A:A,G:G,M:M,S:S").Select
Selection.ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").Select
Selection.ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").Select
Selection.ColumnWidth = 2
Range("E:E,K:K,Q:Q,W:W").Select
Selection.ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").Select
Selection.ColumnWidth = 14

It's obviously less to code but it's also more efficient and quicker
than
the select/selection approach. It doesn't move the selection either
so
you
don't need to reposition afterwards.

You can also say "With Range(....)" rather than "Range(....).Select"
and
"With Selection"

In fact, the whole thing can be reduced to:

Sub Form1()
'
' Form1 Macro
' Macro recorded 12/9/2006 by Donna Casty
' THIS FORMATS MY ROW WIDTHS
' THEN MERGES THE CELLS ACROSS 6 COLUMNS
' AND DOWN 4 ROWS
' I AM TRYING TO CREATE DUPLICATE FORMS ON ONE PAGE.
'
Range("A:A,G:G,M:M,S:S").ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").ColumnWid th = 2
Range("E:E,K:K,Q:Q,W:W").ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").ColumnWidth = 14

With
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:F 64,A65:F65,A66:F66,A67:F67,A85:F85,A86:F86,A87:F87 ,A88:F88,A106:F106,A107:F107,A108:F108,A109:F109,A 127:F127,A128:F128,A129:F129,A130:F130")
.MergeCells = True
End With
With
Range("G1:L1,G2:L2,G3:L3,G4:L4,G22:L22,G23:L23,G24 :L24,G25:L25,G43:L43,G44:L44,G45:L45,G46:L46,G64:L 64,G65:L65,G66:L66,G67:L67,G85:L85,G86:L86,G87:L87 ,G88:L88,G106:L106,G107:L107,G108:L108,G109:L109,G 127:L127,G128:L128,G129:L129,G130:L130")
.MergeCells = True
End With
With
Range("M1:R1,M2:R2,M3:R3,M4:R4,M22:R22,M23:R23,M24 :R24,M25:R25,M43:R43,M44:R44,M45:R45,M46:R46,M64:R 64,M65:R65,M66:R66,M67:R67,M85:R85,M86:R86,M87:R87 ,M88:R88,M106:R106,M107:R107,M108:R108,M109:R109,M 127:R127,M128:R128,M129:R129,M130:R130")
.MergeCells = True
End With
With
Range("S1:X1,S2:X2,S3:X3,S4:X4,S22:X22,S23:X23,S24 :X24,S25:X25,S43:X43,S44:X44,S45:X45,S46:X46,S64:X 64,S65:X65,S66:X66,S67:X67,S85:X85,S86:X86,S87:X87 ,S88:X88,S106:X106,S107:X107,S108:X108,S109:X109,S 127:X127,S128:X128,S129:X129,S130:X130")
.MergeCells = True
End With

End Sub

This assumes that the other format effects are the defaults and don't
need
to be changed.

Regards

Trevor


"Donna C" wrote in message
...
Heres the whole thing so its not so hard to explain, but its kinda
windy,sorry.Try it and see what it does while I try some of the
other
suggestions. Thank you all for helping.
Donna C.
Sub Form1()
'
' Form1 Macro
' Macro recorded 12/9/2006 by Donna Casty
'THIS FORMATS MY ROW WIDTHS
'THEN MERGES THE CELLS ACROSS 6 COLUMNS
'AND DOWN 4 ROWS
'I AM TRYING TO CREATE DUPLICATE FORMS ON ONE PAGE.
'
Range("A:A,G:G,M:M,S:S").Select
Selection.ColumnWidth = 20
Range("B:B,H:H,N:N,T:T").Select
Selection.ColumnWidth = 12
Range("C:C,D:D,I:I,J:J,O:O,P:P,U:U,V:V").Select
Selection.ColumnWidth = 2
Range("E:E,K:K,Q:Q,W:W").Select
Selection.ColumnWidth = 25
Range("F:F,L:L,R:R,X:X").Select
Selection.ColumnWidth = 14


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:F 64,A65:F65,A66:F66,A67:F67,A85:F85,A86:F86,A87:F87 ,A88:F88,A106:F106,A107:F107,A108:F108,A109:F109,A 127:F127,A128:F128,A129:F129,A130:F130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With


Range("G1:L1,G2:L2,G3:L3,G4:L4,G22:L22,G23:L23,G24 :L24,G25:L25,G43:L43,G44:L44,G45:L45,G46:L46,G64:L 64,G65:L65,G66:L66,G67:L67,G85:L85,G86:L86,G87:L87 ,G88:L88,G106:L106,G107:L107,G108:L108,G109:L109,G 127:L127,G128:L128,G129:L129,G130:L130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range("M1:R1,M2:R2,M3:R3,M4:R4,M22:R22,M23:R23,M24 :R24,M25:R25,M43:R43,M44:R44,M45:R45,M46:R46,M64:R 64,M65:R65,M66:R66,M67:R67,M85:R85,M86:R86,M87:R87 ,M88:R88,M106:R106,M107:R107,M108:R108,M109:R109,M 127:R127,M128:R128,M129:R129,M130:R130").Select
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range("S1:X1,S2:X2,S3:X3,S4:X4,S22:X22,S23:X23,S24 :X24,S25:X25,S43:X43,S44:X44,S45:X45,S46:X46,S64:X 64,S65:X65,S66:X66,S67:X67,S85:X85,S86:X86,S87:X87 ,S88:X88,S106:X106,S107:X107,S108:X108,S109:X109,S 127: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

End Sub

"Donna C" wrote:

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.


All times are GMT +1. The time now is 07:28 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com