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.
|