Solved: Problems with ?MERGE? and ?xlCenterAcrossSelection?

[Cyberdude]

I have a macro that creates a small table of data:

.......Col ?A? ............Col ?B?...Col ?C? .Col ?D?....Col ?E?.... Col ?F?
|Willing to Spend | Cost per | Estim |...........| Diff From | Estim |
|......$70,000...... |...Share .|$ Cost |(empty)| $70,000.| Shares |
...................+3% ...$14.80 .$69,565 .............. -$435 .....4700
.................. +2% ...$14.66 .$70,356 ..............+$356 .....4800
.................. +1% ...$14.51..$69,666 ...............-$334 .....4800
. . .

The values in column ?C? are too wide, so I want to either:
A. merge columns ?C? and ?D?, -OR-
B. use .HorizontalAlignment = xlCenterAcrossSelection to give the appearance that column ?C? headers and data are occupying both columns ?C? and ?D?.

When I use the statement:
Range(ColC & SelRow & ":" & ColD & SelRow).Merge (True)
it works, except when it applies to the two header values in column ?C? I get the message:
?The selection contains multiple data values. Merging into one cell will keep the
upper-leftmost data only.?
I reply ?OK? and it proceeds normally. This message does not occur for the data lines, just the headers. I?ve tried to determine what the multiple values are, and I?ve tried to delete all values (if any) in column ?D?. Column ?D? doesn?t seem to have any values. I?ve tried placing the outut is several places on the sheet, and I still get the two messages.

I tried using On Error Resume Next to prevent the merge message from occurring, but I suppose an actual error doesn?t occur, so that was no help.

Next I tried to use .HorizontalAlignment = xlCenterAcrossSelection after selecting the cells in column ?C? and ?D?. I didn?t get a message, but the alignment just worked on column ?C?, i.e., it didn?t spread any of the values across column ?C? and ?D?.

Suggestions anyone?

[lucas]

Why not make column C wider?

[Cyberdude]

Hi, Lucas!
That's an obvious question which I failed to explain ... sorry.
The simple answer is that the width is determined by other factors which cannot be changed. It's kinda like, why don't they make the separation of railroad tracks wider when they build new ones? Too many other factors are dependent on the seperation staying just like it is.

This is a weird problem because it occurs just in the two header rows. There's nothing special about the header. It just has text values instead of currency values. This macro is designed to construct the table anywhere on the sheet that the caller selects a cell with his cursor.

[CBrine]

Cyberdude,
You might want to try this as alternative.

ActiveSheet.Range(ActiveCell, ActiveCell.Offset(0, 1)).HorizontalAlignment = xlCenterAcrossSelection

I find it's less cranky then the merge across.

HTH
Cal

PS- Course when I added something to the second cell, it didn't work, which is most likely what you are going to encounter.

[Zack Barresse]

You can't use On Error Resume Next as it is not a VBA error, it's an application warning. This means you would need to suppress DisplayAlerts from the Application properties (to False), i.e. Application.DisplayAlerts = False. You may have spaces in that location, but they are not technically what the VBIDE would see as Empty. Either suppress that property or clear the other cells (ClearContents) prior to merging. (I cringe when I say to merge any cells...)

[Cyberdude]

Zack, the DisplayAlerts suppression took care of the problem. I was trying to remember that, but never could. Thanks.

I know merging cells is tacky, but I've never had any real problems with them, and that took care of my formatting problem, so ... well now it works. Thanks for the reminder.

You might want to try this as alternative.

ActiveSheet.Range(ActiveCell, ActiveCell.Offset(0, 1)).HorizontalAlignment = xlCenterAcrossSelection

Thanks CB. As I said in my original post, I did try CenterAcrossSelection, but I apparently didn't write my RANGE statement correctly. So now I have a question about your example statement. I guess I'm not familiar with the format you used.

I simplified it a tad to something like:

Range("A1", "B1").HorizontalAlignment = xlCenterAcrossSelection

and it did work. I'm unfamiliar with the Range format where the cells are separated by a comma. When I did it I used a colon, so apparently the CAS was applied to the first cell ("A1") only. What's the story on the comma ... when do you use it?

[SamT]

"What's the story on the comma ... when do you use it?"

Range(a:b) means the range of cells from a to b.

Range(A,B) means the range that consists of all of both Range A and Range B.

Range(A B) {A space B} means the range made up of only the cells common to both ranges A and B.

[Cyberdude]

Range(A B) {A space B} means the range made up of only the cells common to both ranges A and B.

Hi, Sam ... thanks for taking the time to explain.
I finally found the rules in Help, but when I try the third option I keep getting the message "Expected List Separator". Here's the statement I wrote:
[vba]Msgbox Range("A10:C10" "B9:B11")[/vba] It would seem that cell "B10" should be common to the two ranges, but I can't get that far due to the error message. No doubt I wrote the statement incorrectly, but I'm not sure how it's supposed to be written. Any suggestions?

[Zack Barresse]

It should more aptly be described as using Union and Intersect. Intersect includes everything the two ranges include themselves (wherever they meet), whilst a Union includes only the two ranges themselves. You can also include everything between two ranges (including the ranges themselves) with a simple comma. An example...

[VBA]Sub TestRangeDifferences()

Dim rngStart As Range, rngUnion As Range, rngShare As Range, rngIntersect As Range

Set rngStart = Range("A1")
Set rngUnion = Application.Union(rngStart, rngStart.Offset(0, 2))
Set rngShare = Range(rngStart, rngStart.Offset(0, 2))
Set rngIntersect = Application.Intersect(rngShare, Range("C1"))

Debug.Print "CELLS COUNT"
Debug.Print "Start: " & rngStart.Cells.Count & ", " & rngStart.Address(0, 0)
Debug.Print "Union: " & rngUnion.Cells.Count & ", " & rngUnion.Address(0, 0)
Debug.Print "Share: " & rngShare.Cells.Count & ", " & rngShare.Address(0, 0)
Debug.Print "Intersect: " & rngIntersect.Cells.Count & ", " & rngIntersect.Address(0, 0)

End Sub[/VBA]

Think of a Union as attaching two ranges together. The Intersect is where they cross.

HTH

[Cyberdude]

Got it! Thanx, Zack.