I have merged 5 cells horizontally with:

objSht.Range("A1:E1").Merge
objSht.Cells(1, 1) = "My Report Title"

How can I center the report title in the merged cells?

Bill

I detest merging, since it screws up selections, formatting, sorting,
etc. I'd suggest instead that you center across A1:E1. Something like
this:

Dim rOldSelection As Range

Application.ScreenUpdating = False
Set rOldSelection = Selection
Application.Goto objSheet.Range("A1:E1")
With Selection
.Item(1).Value = "My Title"
.HorizontalAlignment = xlCenterAcrossSelection
End With
Application.Goto rOldSelection
Application.ScreenUpdating = True

It's one of the few times I recommend using Select.


In article ,
"Bill Murphy" wrote:

I have merged 5 cells horizontally with:

objSht.Range("A1:E1").Merge
objSht.Cells(1, 1) = "My Report Title"

How can I center the report title in the merged cells?

Try:

With objSht.Range("A1:E1")
.MergeCells = True
.Value = "My Report Title"
.HorizontalAlignment = xlHAlignCenter
End With

Regards,
Greg

-----Original Message-----
I have merged 5 cells horizontally with:

objSht.Range("A1:E1").Merge
objSht.Cells(1, 1) = "My Report Title"

How can I center the report title in the merged cells?

Bill


.

It's one of the few times I recommend using Select.

Is it necessary to Select? This worked for me:

Sub test()
Dim objSheet As Worksheet
Set objSheet = Worksheets("Sheet3")
With objSheet.Range("A1:E1")
..Clear
..HorizontalAlignment = xlCenterAcrossSelection
..VerticalAlignment = xlVAlignCenter
'.BorderAround 1 ' and whatever
..Cells(1).Value = "My Report Title"
End With
End Sub

Regards,
Peter

-----Original Message-----
I detest merging, since it screws up selections,
formatting, sorting,
etc. I'd suggest instead that you center across A1:E1.
Something like
this:

Dim rOldSelection As Range

Application.ScreenUpdating = False
Set rOldSelection = Selection
Application.Goto objSheet.Range("A1:E1")
With Selection
.Item(1).Value = "My Title"
.HorizontalAlignment = xlCenterAcrossSelection
End With
Application.Goto rOldSelection
Application.ScreenUpdating = True

It's one of the few times I recommend using Select.


In article ,
"Bill Murphy" wrote:

I have merged 5 cells horizontally with:

objSht.Range("A1:E1").Merge
objSht.Cells(1, 1) = "My Report Title"

How can I center the report title in the merged cells?
.

No, it's not.

For me it's a totemic holdover - I had problems with it years ago in a
previous version of XL (I don't recall why, but I recall it as a project
from hell), so ever since I've used a custom function that actually does
the selection.

Time for me to lose that superstition.




In article ,
"Peter T" wrote:

It's one of the few times I recommend using Select.

Is it necessary to Select? This worked for me:

For me it's a totemic holdover

I have quite a few of those, never found quite the right
expression to capture so succinctly. I'll use it at the
earliest opportunity :)

Peter

Thanks to all of you for your solutions, all of which work!

Bill


"Bill Murphy" wrote in message
...
I have merged 5 cells horizontally with:

objSht.Range("A1:E1").Merge
objSht.Cells(1, 1) = "My Report Title"

How can I center the report title in the merged cells?

Bill