How do I merge entire columns without merging the rows?

[Guest]

I am using Excel to produce report from a VB front end. The report can
contain hundreds of sections and each section has two columns that are merged
together. I take a huge perfomance hit by having to merge the cells for each
row of data individually, so I would like to be able to select the entire two
columns and merge them together at once. However, doing this also merges all
the rows together so I get one HUGE cell that spans the entire length of the
report.

ActiveSheet.Columns(A:B).Merge ' this merges everything

Does anyone have any idea how to merge columns without merging the rows too?

 

[Dave Peterson]

Oooh. If you looked at help, you would have seen that .merge has a parameter
that tells it to merge it one row at a time:

Application.DisplayAlerts = False
ActiveSheet.Range("A:B").Merge across:=True
Application.DisplayAlerts = True

I cheated though.

I've added a button to my favorite toolbar that merges row by row.
tools|customize|commands tab|format category
Look for the "Merge Across" icon.

(Then I just recorded a macro when I did it manually.)

 

[Guest]

Geeze, I can't believe I didn't see that in the help ...just a little
exhausted these days, I guess. Thank you very much for the response!

 

[Guest]

Oh boy, I spoke too soon. When I tested it with

ActiveSheet.Range("A:B").Merge across:=True

only the first row in the two columns gets merged. However, if I specify a
range of cells like

ActiveSheet.Range("A1:B10").Merge across:=True

then each row's columns get merged as desired.

 

[Dave Peterson]

It looks like this is limited to the usedrange.

So you could extend the used range to row 65536, but that will increase the size
of your workbook.

I think I'd pick a row that was large enough for what I needed, then add
something in that row, do the merge, and clean up that row.

With activesheet
.range("a1000").value = "xxx"
Application.DisplayAlerts = False
.Range("A:B").Merge across:=True
Application.DisplayAlerts = true
.range("a1000").value = ""
end with

(Doing all 64k rows locked up excel for me.)