macro for merging vertically

[the second]

I would like to ask a favor to somebody here to write for
me a macro for merging vertically all the cells in a
selection.
There are two pre-programmed commands in in Excel:
1. merge all cells in a selection
2. merge across, which merges only horizontally all cells
in selection

I would like a macro that works in the same way as merge
across but does it vertically.

Thank you very much to whomever is going to help me.

 

[Paul B]

If you select cells in a column and merge it merges them vertically, doesn'
t? anyway try this

With Selection
.MergeCells = True
End With

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2000 & 97
** remove news from my email address to reply by email **

 

[Greg Wilson]

Is this what you want?

Sub MergeVert()
Dim Col As Range, MergeRng As Range
For Each Col In Selection.Columns
Set MergeRng = Application.Intersect(Col, Selection)
MergeRng.MergeCells = True
Next
End Sub

Regards,
Greg

 

[David McRitchie]

To merge separate areas vertically the following
might be more suitable. Only merge within an area.
http://www.mvps.org/dmcritchie/excel/merge.htm

i.e. A1:B4, F1:B4, A10:B14, F10:B14

Sub MergeCxC()
'-- Merge cells in multiple selected areas Column by Column ---
' limited to the usedrange (Ctrl+End)
' D.McRitchie, 2002-05-31 in merge.htm
Dim rng As Range
Dim rw As Range, ix As Long
Set rng = Intersect(Selection, ActiveSheet.UsedRange)
If rng Is Nothing Then
MsgBox "nothing in usedrange to be merged"
GoTo done
End If
Dim i As Long, j As Long
For i = 1 To Selection.Areas.Count
For j = 1 To Selection.Areas(i).Columns.Count
Application.DisplayAlerts = False
Selection.Areas(i).columnss(j).MergeCells = True
Application.DisplayAlerts = True
Next
Next
done:
End Sub

I think you would get a lot more out of newsgroups if you
used your name, particularly in technical and business
newsgroups.

 

[Greg Wilson]

Hi David,

Issue 1:
To be honest, I miss the point of using a macro
altogether, whether mine or yours. If you use the <Ctrl>
button to individually select separate blocks of cells
then Excel treats these as separate areas whether
contiguous or not. You can separately select single
column vertical cell blocks, single row horizontal cell
blocks, multi-column (or multi-row) cell blocks. The
selection process thus identifies the merge ranges. You
can then manually merge these multiple cell blocks as
separate units (Format|Cells|Alignment tab|Merge cells
check box). For instance, if you want to merge the
following ranges as separage blocks then select them
separately using the <Ctrl> key. Then manually merge them
as a single action:
A1:A5 (single column vertical merge)
B1:B5 (single column vertical merge)
D1:F4 (combined merge)
E4:H4 (single row horizontal merge)

This gives you more control than a hard coded macro that
only does single column (or single row) merges and you
don't have to keep a macro on hand.

Issue 2:
Is listing my email address in my post what you meant by
using my name? I naively used my real email address on
DevDex and got spammed to death to the point that my email
is now nearly useless.

Regards,
Greg

 

[The Second]

Thank you to all three of you.

Greg provided the solution I was looking for.
I apologize to the others if my unclear explanation made
you do work for nothing.
I learnt from this thread how to ask more clearly (i.e.
making references to cell numbers)

Thank you again, you saved me hours of very tedious work
and I appreciate that.

 

[Greg Wilson]

I forgot to mention that my code also will vertically
merge multiple areas. It doesn't seem necessary to
programmatically act on each area separately in your
code. For example, if you select the ranges A1:C5, D1:F5,
G1:I5 using the <Ctrl> button and run my macro then all
cells will be vertically merged.

Regards,
Greg

 

[David McRitchie]

Hi Greg,
You are correct you can do them with the selection
area in a macro without processing individual areas.
But not the same manually unless you select cells
in one column at a time. (won't worry about XL 95).

Trying to individually select 30 columns would be
rather tedious, the macros certainly makes that easier.

I thought mine would handle overlapping areas,
but it just makes them into bigger areas when
overlapping, including extra cells.

For the merging cells on a per row basis there
is a tool bar button, "merge across", but nothing
comparable for merging columns. I didn't know
about the toolbar button when I wrote the macros.

Also didn't realize MergeCxC had been sitting around
with columns as columnss (now corrected).