PDA

View Full Version : Solved: Merge Cells



peterwmartin
01-04-2007, 10:14 PM
Hi all,
I am trying to check if the length of a string is greater than a number then merge the cells and center.
Sub try1()
Dim leng As Integer
Dim x As String
x = Range("a4")
Range("c4").Value = Len(x)
If Len(x) > 10 Then Range("a4").Merge (xlRight) 'Insert Shift:=xlToRight
'Selection.Insert Shift:=xlToRight
'.MergeCells = True
'.HorizontalAlignment = xlCenter
End Sub

Can someone help where I haxe xlright microsofts example has ([across]).
what do i subsitute? This is a stepping stone I would like to check a range say a1:m600 and have it happen for each cell that has > than length.
Any advice would be appreciated:doh:

JimmyTheHand
01-04-2007, 11:35 PM
Hi
You have not specified what cells you want to merge with A4. If A4 is a corner of the to-be-merged area, then the code below should work.

Sub try1()
Dim leng As Integer 'why declare if don't use it
Dim x As String
x = Range("A4")
Range("c4").Value = Len(x)
If Len(x) > 10 Then
With Range("A4").Resize(RIx, CIx)
.MergeCells = True
.HorizontalAlignment = xlCenter
End With
End If
End Sub
Replace RIx and CIx with the appropriate number of rows and columns to be merged. E.g. if RIx = 1 and CIx = 3 then Range("A4:C4") will be merged

peterwmartin
01-04-2007, 11:53 PM
Thanks Jimmy,
I guess in a couple of days I may have got the correct code myself.
I will now use it and work thru the remainder of the selection.
thanks

Ken Puls
01-05-2007, 11:04 AM
Can I ask? Why Merge and Center? Why not just center across the selection?

Merged cells cause all kinds of issues with code, and should be avoided if at all possible.

lucas
01-05-2007, 11:17 AM
I agree with Ken........no merged cells.......ever...well there has to be a very good reason.

JimmyTheHand
01-05-2007, 11:22 AM
Why not just center across the selection?
How do you do that?

EDIT
Nevermind, I got it now.

Ken Puls
01-05-2007, 11:50 AM
Just for others who don't know...

The manual way to do this is as follows:
-Highlight the cells you want to center accross
-Right click and go to Format Cells|Alignment
-In the "Horizontal" box, choose "Center Across Selection"

The effect usually appears the same as merged cells, without blowing apart Excel's functionality. For example:
-You cannot insert a column through merged cells. With CAS, you can and it will automatically adjust the centering across the new range of cells
-You can't always copy/paste without the cells unmerging
-Many macros hang if they haven't been written to deal with it

Now.. funny enough, I was interupted writing this reply because one of my users had an issue. They were using a large spreadsheet, and were trying to unhide one column that was between merged cell sets. When they higlighted the columns on either side, it selected the entire sheet and crashed Excel. Carefully changing all the merged cells to CAS fixed the issue.

Just my 2 cents on it, but I really hate merged cells, and avoid them wherever possible. I'm not saying they have no use, but be very sure there isn't another way before you start applying them. :)

JimmyTheHand
01-05-2007, 12:09 PM
Thanks, Ken.
And can you do the same vertically? I haven't found the way.

Ken Puls
01-05-2007, 01:16 PM
Unfortunately, not that I'm aware of.

To be honest, the one time that we've ever needed to have a vertical overlap here, we did it by using wordart. Hardly a way I'd recommend, but it floated over our regular cells which served our purpose well in that instance.

peterwmartin
01-05-2007, 05:56 PM
Thanks Jimmy why not?
Because I didn't know you could but will now help me heaps. Spent most of the night wishing I didn't have to merge cells. Lots of problems got you message the day after.
Thank you very very much all.

peterwmartin
01-05-2007, 05:58 PM
Sorry Ken did not realize first the question was asked by you. Thank you

Ken Puls
01-08-2007, 10:51 AM
No worries, Peter.

Honestly, I'm just happy you got something out of it. If I could educate just one user per day about the evils of merged cells, I think that I'll guarantee myself a trip to a better place one day. ROFL! :)