How do I merge entire columns without merging the rows?

[Dean_Bradko]

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.)



Dean_Bradko wrote:
>
> 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

[Dean_Bradko]

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!

"Dave Peterson" wrote:

> 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.)
>
>
>
> Dean_Bradko wrote:
> >
> > 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
>

[Dean_Bradko]

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" wrote:

> 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.)
>
>
>
> Dean_Bradko wrote:
> >
> > 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
>

[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.)

Dean_Bradko wrote:
>
> 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" wrote:
>
> > 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.)
> >
> >
> >
> > Dean_Bradko wrote:
> > >
> > > 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
> >

--

Dave Peterson