Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for merging vertically
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for merging vertically
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 ** "the second" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for merging vertically
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 -----Original Message----- 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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for merging vertically
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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Greg Wilson" wrote l... 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 "the second" wrote in 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for merging vertically
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 -----Original Message----- 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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Greg Wilson" wrote l... 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 "the second" wrote in 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. . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for merging vertically
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for merging vertically
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro for merging vertically
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). --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm "Greg Wilson" wrote in ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Overlap Cells Vertically without Merging | Excel Discussion (Misc queries) | |||
Merging Macro | Excel Discussion (Misc queries) | |||
need urgent help on macro merging | Excel Discussion (Misc queries) | |||
Help with Worksheet Merging Macro | New Users to Excel | |||
list sheet names vertically below the active cell - need macro. | Excel Worksheet Functions |