How do I merge entire columns without merging the rows?

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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.)
 
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!
 
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.
 
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.)
 
Back
Top