software

In MS Excel, when we merge cells, content of only one cell exists.

But is MS word, in a table, we can merger any number of cells without
removing content of any cell.

If this feature is dependant on certain settings, please let me know.

Otherwise, it will be good to introduce this functionality in MS Excel also.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the quot;I
Agreequot; button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click quot;I Agreequot; in the message pane.

www.microsoft.com/office/comm...lic.excel.misc

Hi Rumpa,
When Word merges table cells it looks to me like it just gets rid of
the border separating the cells.
This same effect can be achieved in Excel by formatting the borders.
Merging of cells in Excel hides the cell values in all but the first
(top or left) cell, their contents are not removed.
Excel cell merging is not good practice, it causes all sorts of
problems when processing data in them.

Ken JohnsonHow should it join the values in the now merged cells.

Should it use the text that you see?

1234.00 formatted as $1,234.00 or keep it as the value in the cell. (Remember
that dates can be formatted lots of different ways, too.)

Should it have a space between each value or should it just jam things
together? Or maybe add alt-enters between each value.

If you're always merging the cells the same way, you could create a macro that
did what you wanted with the values in that area to be merged.

Then just select that range to merge and execute the macro.

Rumpa Biswas wrote:
gt;
gt; In MS Excel, when we merge cells, content of only one cell exists.
gt;
gt; But is MS word, in a table, we can merger any number of cells without
gt; removing content of any cell.
gt;
gt; If this feature is dependant on certain settings, please let me know.
gt;
gt; Otherwise, it will be good to introduce this functionality in MS Excel also.
gt;
gt; ----------------
gt; This post is a suggestion for Microsoft, and Microsoft responds to the
gt; suggestions with the most votes. To vote for this suggestion, click the quot;I
gt; Agreequot; button in the message pane. If you do not see the button, follow this
gt; link to open the suggestion in the Microsoft Web-based Newsreader and then
gt; click quot;I Agreequot; in the message pane.
gt;
gt; www.microsoft.com/office/comm...lic.excel.misc

--

Dave Peterson

Hi Ken,
Thank for yor reply. But when cells are merged in MS Word, it creates a
merged cell with all texts of different cells separated by a line-break.
But in excel only content of top-left cel remains within merged cells.
It is true that sorting and certain other operations are not possible for
merged cells. But merging of cells can be used fro different purpose.
At times I need content of all cells together for some report etc. Then I
always copy into MS word, merge cells there, then use the content.
I was wondering if there is any way in Excel.

Rumpa Biswas

quot;Ken Johnsonquot; wrote:

gt; Hi Rumpa,
gt; When Word merges table cells it looks to me like it just gets rid of
gt; the border separating the cells.
gt; This same effect can be achieved in Excel by formatting the borders.
gt; Merging of cells in Excel hides the cell values in all but the first
gt; (top or left) cell, their contents are not removed.
gt; Excel cell merging is not good practice, it causes all sorts of
gt; problems when processing data in them.
gt;
gt; Ken Johnson
gt;
gt;

Hi Dave,
Thanks for your reply. I am not aware of the fact that macros can be used
for merging cells without removing any content.
If you have any such macro ready with you, it will be nice if you please
share with me.

quot;Dave Petersonquot; wrote:

gt; How should it join the values in the now merged cells.
gt;
gt; Should it use the text that you see?
gt;
gt; 1234.00 formatted as $1,234.00 or keep it as the value in the cell. (Remember
gt; that dates can be formatted lots of different ways, too.)
gt;
gt; Should it have a space between each value or should it just jam things
gt; together? Or maybe add alt-enters between each value.
gt;
gt; If you're always merging the cells the same way, you could create a macro that
gt; did what you wanted with the values in that area to be merged.
gt;
gt; Then just select that range to merge and execute the macro.
gt;
gt; Rumpa Biswas wrote:
gt; gt;
gt; gt; In MS Excel, when we merge cells, content of only one cell exists.
gt; gt;
gt; gt; But is MS word, in a table, we can merger any number of cells without
gt; gt; removing content of any cell.
gt; gt;
gt; gt; If this feature is dependant on certain settings, please let me know.
gt; gt;
gt; gt; Otherwise, it will be good to introduce this functionality in MS Excel also.
gt; gt;
gt; gt; ----------------
gt; gt; This post is a suggestion for Microsoft, and Microsoft responds to the
gt; gt; suggestions with the most votes. To vote for this suggestion, click the quot;I
gt; gt; Agreequot; button in the message pane. If you do not see the button, follow this
gt; gt; link to open the suggestion in the Microsoft Web-based Newsreader and then
gt; gt; click quot;I Agreequot; in the message pane.
gt; gt;
gt; gt; www.microsoft.com/office/comm...lic.excel.misc
gt;
gt; --
gt;
gt; Dave Peterson
gt;

Hi Rumpa,
First I have to correct myself. I was saying that when cells are merged
in Excel all but the top-left cell have their values hidden, not
deleted. I now see that those cell values are deleted, not just hidden.
This means that those cell values will not reappear if after merging
you then go Formatgt;Cellsgt;Alignment, then remove the tick from Merge
cells. However, if instead of back-tracking to remove the tick you
click on Undo (or Control Z) those cell values reappear. This is a
special case that only works when done immediately after applying the
cell merging.

I have put together a macro that I think, and hope, will merge cells in
Excel the way you have described the way Word merges cells.

Test the macro out on a backup copy of your file first (changes the
macro makes cannot be undone, unless you close your file without
saving).

First select the range of cells that you want merged , then run the
macro.
The code places each selected cell value into the selection's
top-left cell. Column values are separated by a single space. Row
values are separated by a line break (same as Alt Enter). Those cells
are then merged.

It works quite well when merging rows from one column. Merging columns
is not so good visually. I don't know of an easy way of lining up the
different column values within an Excel cell. Excel cells, unlike Word
cells, do not have a tab that could be used to line up the
corresponding column values from the different rows.

Public Sub MergeLikeWord()
Application.ScreenUpdating = False
Dim iRows As Long
Dim iColumns As Long
Dim vaMergeArray As Variant
Dim I As Long, J As Long
Dim stDisplayedText As String
Dim stMergeColumns() As String
vaMergeArray = Selection
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
If iRows = 1 And iColumns = 1 Then Exit Sub
ReDim stMergeColumns(1 To iRows)
For I = 1 To iRows
For J = 1 To iColumns
stMergeColumns(I) = stMergeColumns(I) _
amp; vaMergeArray(I, J) _
amp; IIf(J lt; iColumns, Space(1), quot;quot;)
Next J
Next I
For I = 1 To iRows
stDisplayedText = stDisplayedText _
amp; stMergeColumns(I) _
amp; IIf(I lt; iRows, Chr(10), quot;quot;)
Next I
With ActiveCell
.Value = stDisplayedText
.VerticalAlignment = xlTop
End With
Application.DisplayAlerts = False
With Selection
.Merge
.Rows.AutoFit
End With
Application.DisplayAlerts = True
End Sub

I hope this helps you solve your problem.
Happy New Year (1hour 17minutes to go)

Ken JohnsonHi Ken,
Thanks for your reply. I will try the macro for sure.

Wishing you too a very happy New Year.

- Rumpa

quot;Ken Johnsonquot; wrote:

gt; Hi Rumpa,
gt; First I have to correct myself. I was saying that when cells are merged
gt; in Excel all but the top-left cell have their values hidden, not
gt; deleted. I now see that those cell values are deleted, not just hidden.
gt; This means that those cell values will not reappear if after merging
gt; you then go Formatgt;Cellsgt;Alignment, then remove the tick from Merge
gt; cells. However, if instead of back-tracking to remove the tick you
gt; click on Undo (or Control Z) those cell values reappear. This is a
gt; special case that only works when done immediately after applying the
gt; cell merging.
gt;
gt; I have put together a macro that I think, and hope, will merge cells in
gt; Excel the way you have described the way Word merges cells.
gt;
gt; Test the macro out on a backup copy of your file first (changes the
gt; macro makes cannot be undone, unless you close your file without
gt; saving).
gt;
gt; First select the range of cells that you want merged , then run the
gt; macro.
gt; The code places each selected cell value into the selection's
gt; top-left cell. Column values are separated by a single space. Row
gt; values are separated by a line break (same as Alt Enter). Those cells
gt; are then merged.
gt;
gt; It works quite well when merging rows from one column. Merging columns
gt; is not so good visually. I don't know of an easy way of lining up the
gt; different column values within an Excel cell. Excel cells, unlike Word
gt; cells, do not have a tab that could be used to line up the
gt; corresponding column values from the different rows.
gt;
gt; Public Sub MergeLikeWord()
gt; Application.ScreenUpdating = False
gt; Dim iRows As Long
gt; Dim iColumns As Long
gt; Dim vaMergeArray As Variant
gt; Dim I As Long, J As Long
gt; Dim stDisplayedText As String
gt; Dim stMergeColumns() As String
gt; vaMergeArray = Selection
gt; iRows = Selection.Rows.Count
gt; iColumns = Selection.Columns.Count
gt; If iRows = 1 And iColumns = 1 Then Exit Sub
gt; ReDim stMergeColumns(1 To iRows)
gt; For I = 1 To iRows
gt; For J = 1 To iColumns
gt; stMergeColumns(I) = stMergeColumns(I) _
gt; amp; vaMergeArray(I, J) _
gt; amp; IIf(J lt; iColumns, Space(1), quot;quot;)
gt; Next J
gt; Next I
gt; For I = 1 To iRows
gt; stDisplayedText = stDisplayedText _
gt; amp; stMergeColumns(I) _
gt; amp; IIf(I lt; iRows, Chr(10), quot;quot;)
gt; Next I
gt; With ActiveCell
gt; .Value = stDisplayedText
gt; .VerticalAlignment = xlTop
gt; End With
gt; Application.DisplayAlerts = False
gt; With Selection
gt; .Merge
gt; .Rows.AutoFit
gt; End With
gt; Application.DisplayAlerts = True
gt; End Sub
gt;
gt; I hope this helps you solve your problem.
gt; Happy New Year (1hour 17minutes to go)
gt;
gt; Ken Johnson
gt;
gt;

Try selecting your range and running this:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myArea As Range

Set myRng = Selection

Application.DisplayAlerts = False
For Each myArea In myRng.Areas
If myArea.Columns.Count gt; 1 Then
myArea.Merge across:=True
End If
Next myArea
Application.DisplayAlerts = True

End SubIt merges each row separate from the other rows.

Rumpa Biswas wrote:
gt;
gt; Hi Dave,
gt; Thanks for your reply. I am not aware of the fact that macros can be used
gt; for merging cells without removing any content.
gt; If you have any such macro ready with you, it will be nice if you please
gt; share with me.
gt;
gt; quot;Dave Petersonquot; wrote:
gt;
gt; gt; How should it join the values in the now merged cells.
gt; gt;
gt; gt; Should it use the text that you see?
gt; gt;
gt; gt; 1234.00 formatted as $1,234.00 or keep it as the value in the cell. (Remember
gt; gt; that dates can be formatted lots of different ways, too.)
gt; gt;
gt; gt; Should it have a space between each value or should it just jam things
gt; gt; together? Or maybe add alt-enters between each value.
gt; gt;
gt; gt; If you're always merging the cells the same way, you could create a macro that
gt; gt; did what you wanted with the values in that area to be merged.
gt; gt;
gt; gt; Then just select that range to merge and execute the macro.
gt; gt;
gt; gt; Rumpa Biswas wrote:
gt; gt; gt;
gt; gt; gt; In MS Excel, when we merge cells, content of only one cell exists.
gt; gt; gt;
gt; gt; gt; But is MS word, in a table, we can merger any number of cells without
gt; gt; gt; removing content of any cell.
gt; gt; gt;
gt; gt; gt; If this feature is dependant on certain settings, please let me know.
gt; gt; gt;
gt; gt; gt; Otherwise, it will be good to introduce this functionality in MS Excel also.
gt; gt; gt;
gt; gt; gt; ----------------
gt; gt; gt; This post is a suggestion for Microsoft, and Microsoft responds to the
gt; gt; gt; suggestions with the most votes. To vote for this suggestion, click the quot;I
gt; gt; gt; Agreequot; button in the message pane. If you do not see the button, follow this
gt; gt; gt; link to open the suggestion in the Microsoft Web-based Newsreader and then
gt; gt; gt; click quot;I Agreequot; in the message pane.
gt; gt; gt;
gt; gt; gt; www.microsoft.com/office/comm...lic.excel.misc
gt; gt;
gt; gt; --
gt; gt;
gt; gt; Dave Peterson
gt; gt;

--

Dave Peterson

This will quot;Preservequot; the formatted values:

Option Explicit
Sub testme02()

Dim myRng As Range
Dim myArea As Range
Dim myRow As Range
Dim myCell As Range
Dim myStr As String

Set myRng = Selection

Application.DisplayAlerts = False
For Each myArea In myRng.Areas
If myArea.Columns.Count gt; 1 Then
For Each myRow In myArea.Rows
myStr = quot;quot;
For Each myCell In myRow.Cells
myStr = myStr amp; quot; quot; amp; myCell.Text
Next myCell
myRow.Merge across:=True
myRow.Cells(1).Value = Mid(myStr, 2)
Next myRow
End If
Next myArea
Application.DisplayAlerts = True

End Sub

Dave Peterson wrote:
gt;
gt; Try selecting your range and running this:
gt;
gt; Option Explicit
gt; Sub testme02()
gt;
gt; Dim myRng As Range
gt; Dim myArea As Range
gt;
gt; Set myRng = Selection
gt;
gt; Application.DisplayAlerts = False
gt; For Each myArea In myRng.Areas
gt; If myArea.Columns.Count gt; 1 Then
gt; myArea.Merge across:=True
gt; End If
gt; Next myArea
gt; Application.DisplayAlerts = True
gt;
gt; End Sub
gt;
gt; It merges each row separate from the other rows.
gt;
gt; Rumpa Biswas wrote:
gt; gt;
gt; gt; Hi Dave,
gt; gt; Thanks for your reply. I am not aware of the fact that macros can be used
gt; gt; for merging cells without removing any content.
gt; gt; If you have any such macro ready with you, it will be nice if you please
gt; gt; share with me.
gt; gt;
gt; gt; quot;Dave Petersonquot; wrote:
gt; gt;
gt; gt; gt; How should it join the values in the now merged cells.
gt; gt; gt;
gt; gt; gt; Should it use the text that you see?
gt; gt; gt;
gt; gt; gt; 1234.00 formatted as $1,234.00 or keep it as the value in the cell. (Remember
gt; gt; gt; that dates can be formatted lots of different ways, too.)
gt; gt; gt;
gt; gt; gt; Should it have a space between each value or should it just jam things
gt; gt; gt; together? Or maybe add alt-enters between each value.
gt; gt; gt;
gt; gt; gt; If you're always merging the cells the same way, you could create a macro that
gt; gt; gt; did what you wanted with the values in that area to be merged.
gt; gt; gt;
gt; gt; gt; Then just select that range to merge and execute the macro.
gt; gt; gt;
gt; gt; gt; Rumpa Biswas wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; In MS Excel, when we merge cells, content of only one cell exists.
gt; gt; gt; gt;
gt; gt; gt; gt; But is MS word, in a table, we can merger any number of cells without
gt; gt; gt; gt; removing content of any cell.
gt; gt; gt; gt;
gt; gt; gt; gt; If this feature is dependant on certain settings, please let me know.
gt; gt; gt; gt;
gt; gt; gt; gt; Otherwise, it will be good to introduce this functionality in MS Excel also.
gt; gt; gt; gt;
gt; gt; gt; gt; ----------------
gt; gt; gt; gt; This post is a suggestion for Microsoft, and Microsoft responds to the
gt; gt; gt; gt; suggestions with the most votes. To vote for this suggestion, click the quot;I
gt; gt; gt; gt; Agreequot; button in the message pane. If you do not see the button, follow this
gt; gt; gt; gt; link to open the suggestion in the Microsoft Web-based Newsreader and then
gt; gt; gt; gt; click quot;I Agreequot; in the message pane.
gt; gt; gt; gt;
gt; gt; gt; gt; www.microsoft.com/office/comm...lic.excel.misc
gt; gt; gt;
gt; gt; gt; --
gt; gt; gt;
gt; gt; gt; Dave Peterson
gt; gt; gt;
gt;
gt; --
gt;
gt; Dave Peterson

--

Dave Peterson


Another option would be to concatenate your cells into a single string.

Regards
JG--
pinmaster
------------------------------------------------------------------------
pinmaster's Profile: www.excelforum.com/member.php...foamp;userid=6261
View this thread: www.excelforum.com/showthread...hreadid=496332