Macro issue with Merging Non-Contiguous Columns

[Guest]

I am creating an excel spreadsheet the produces various financial reports for
a client.

I have attached the following code to a control button.

When clicked the macro does the following:

1) Goes to a report sheet;
2) Hides certain columns in the sheet (the sheet contains many columns of
data which are only shown depending on the report the client wants to run)
3) Unhides only those columns that are relevant for the report the client
is running i.e. Month To Date and Year to Date information in this case)
4) Then the macro formats the TITLE rows so that they are merged across the
columns and thus centred on the report.

My problem comes about when dealing with non-contiguous column ranges and
the merging of the cells.

In the below example the MTDYTDReport named range includes columns D, F and H.

Therefore for some reason when the TITLE rows are merged they are not being
merged across all the columns.

Does anyone have an idea of how I can get around this??


Sub MTDYTDReport()
'
' This macro unhides the columns for the MTD, YTD Report
' Macro recorded 05/05/2006 by JTE
'
Sheets("P & L Branch").Select
Range("AllPLBranch").Select
Selection.EntireColumn.Hidden = True
Range("MTDYTDReport").Select
Selection.EntireColumn.Hidden = False
Range("MTDYTDReport1").Select
Selection.EntireColumn.Hidden = False

'This section will merge the titles to fit across all columns of report
Range("A2:X2").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Range("A3:X3").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Range("A4:X4").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With
Range("A5:X5").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With

Range("A1").Select
End Sub

 

[Jim Cone]

You need to stick the horizontal alignment property in there someplace...
Selection.HorizontalAlignment = xlHAlignCenterAcrossSelection

Also, you don't have to merge the cells in order to center the text across
the columns. Just use the above line without the merge.
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware



"James T"
<[email protected]>
wrote in message
I am creating an excel spreadsheet the produces various financial reports for
a client.
I have attached the following code to a control button.
When clicked the macro does the following:

1) Goes to a report sheet;
2) Hides certain columns in the sheet (the sheet contains many columns of
data which are only shown depending on the report the client wants to run)
3) Unhides only those columns that are relevant for the report the client
is running i.e. Month To Date and Year to Date information in this case)
4) Then the macro formats the TITLE rows so that they are merged across the
columns and thus centred on the report.
My problem comes about when dealing with non-contiguous column ranges and
the merging of the cells.
In the below example the MTDYTDReport named range includes columns D, F and H.

Therefore for some reason when the TITLE rows are merged they are not being
merged across all the columns.
Does anyone have an idea of how I can get around this??


Sub MTDYTDReport()
'
' This macro unhides the columns for the MTD, YTD Report
' Macro recorded 05/05/2006 by JTE
'
Sheets("P & L Branch").Select
Range("AllPLBranch").Select
Selection.EntireColumn.Hidden = True
Range("MTDYTDReport").Select
Selection.EntireColumn.Hidden = False
Range("MTDYTDReport1").Select
Selection.EntireColumn.Hidden = False

'This section will merge the titles to fit across all columns of report
Range("A2:X2").Select
With Selection
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = True
End With

-snip-

 

[Guest]

Jim - Thank you. Simple solutions are the best.

Your help is appreciated.

Regards

James