Merging Cells.. Lots of the little buggers

[Guest]

Hello All,
I tried to do this with the macro recorder...the code is about 4 miles long.

I am trying to merge cells N17-U17 then goto the next row down and do the
same thing ie: N18-U18 all the way down to N45-U45.

I'd hazzard a guess there's a simple loop I could be using instead.

Any help appreciated.

Peter

 

[Ken Wright]

First things first, why would you do this. I don't think there is a single
regular in here that wouldn't advise you to stay away from merged cells if
at all possible, They really do cause an awful lot of problems wrt
referencing, formulas etc. Centering across selection will give the same
visual effect as what you are trying to do.

If you really need to go there though, then one example

Sub MergeCells()

For x = 3 To 30
Range("A" & x).Resize(1, 2).MergeCells = True
Next x

End Sub

 

[Ken Wright]

Obviously just amend the ranges to suit (Would probably have been better if
I'd just done it to suit your data in the first place)

Sub MergeCells()

For x = 17 To 45
Range("N" & x).Resize(1, 8).MergeCells = True
Next x

End Sub

 

[Guest]

Thanks Ken,
I never knew there was another way of doing it to tell the truth.

In this one particular section of my worksheet, it has to be changed to 3
totally diffrent looking sections, some with lines, some with no lines etc
etc. Then of course what goes into them is diffrent too..no formula's though,
just pure input from the poor old keyboard.

Is it possible for you to give me a link that describes the
alternative..might just simplify my life somewhat.

Regards
Peter

 

[Ken Wright]

As far as lines vs no lines, simply insert borders and format them as white,
OR, insert a white pattern into the cells which will obliterate the borders
and give the effect of no lines.

Re centering, just select a range of cells, eg N17:U17, do Format / cells /
Alignment tab / Horizontal / center across selection.

Amending the example I gave you

Sub MergeCells()

For x = 17 To 45
Range("N" & x).Resize(1, 8).HorizontalAlignment =
xlCenterAcrossSelection
Next x

End Sub

 

[Guest]

Thanks Ken,
You just took another week off this darn sheet I am working on..where were
you 2 weeks ago.

Regards
Peter

 

[Ken Wright]

LOL - We were all sat right here - Sad muppets the lot of us, but that's
what we like doing

 

[Dave Peterson]

This worked for me in xl2002:

Range("N17:U45").Merge True

The True portion says to do it one row at a time.

(But I like to stay away from merged cells, too.)

 

[Ken Wright]

LOL - learn something new everyday - Hate the result, but like knowing a
different way to do it Cheers Dave.

 

[Jon Peltier]

In general, merged cells are a PITA. But last summer I made an elegant
in-sheet gantt chart with lots of funky formatting, labels, and other
effects which were impossible in an Excel chart, and impossible without
merging cells. And the computer never choked on the merged cells. Every
time the program updated the chart, the old one was discarded and the
new one was built from scratch. It took no longer this way, but probably
threw away the accumulated rubbish that makes these things so unstable.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[Nicolas Noakes]

Beat me to it Dave!

In Excel, if you open the "customize toolbars" dialog, you should find a
button under the format menu called "merge across" which you could add
to any toolbar - this manually does what Dave's code does. I was
intending to suggest recording what it did.

Rgds,
Nicolas

Dave Peterson wrote:

This worked for me in xl2002:

Range("N17:U45").Merge True

The True portion says to do it one row at a time.

(But I like to stay away from merged cells, too.)

 

[Ken Wright]

Hi Jon - would be interested to see it if you still have a copy?