Excel should merge cells without removing content of any cell

[Guest]

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 "I
Agree" 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 "I Agree" in the message pane.

http://www.microsoft.com/office/com...a-f9f84246a43d&dg=microsoft.public.excel.misc

 

[Ken Johnson]

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 Johnson

 

[Dave Peterson]

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

 

[Guest]

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

 

[Guest]

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.

 

[Ken Johnson]

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 Format>Cells>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) _
& vaMergeArray(I, J) _
& IIf(J < iColumns, Space(1), "")
Next J
Next I
For I = 1 To iRows
stDisplayedText = stDisplayedText _
& stMergeColumns(I) _
& IIf(I < iRows, Chr(10), "")
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 Johnson

 

[Guest]

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

Wishing you too a very happy New Year.

- Rumpa

 

[Dave Peterson]

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 > 1 Then
myArea.Merge across:=True
End If
Next myArea
Application.DisplayAlerts = True

End Sub


It merges each row separate from the other rows.

 

[Dave Peterson]

This will "Preserve" 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 > 1 Then
For Each myRow In myArea.Rows
myStr = ""
For Each myCell In myRow.Cells
myStr = myStr & " " & 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

 

[pinmaster]

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

Regards
JG

 

[Guest]

Hi Ken,
I couldn't get this one to work. I have the same need that Rumpa does
except I need to have the text from column "b" cut from the cell and then
pasted on the end of the text in column "a". If I merge them I am not able
to delete column "b" and it messes up my export to CSV files.
Would appreciate any help you can give.
Thanks,
Richard

 

[Dave Peterson]

One more variation. It won't add extra spaces if there are empty cells:

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 > 1 Then
For Each myRow In myArea.Rows
myStr = ""
For Each myCell In myRow.Cells
If myCell.Text <> "" Then
myStr = myStr & " " & myCell.Text
End If
Next myCell
myRow.Merge across:=True
myRow.Cells(1).Value = Mid(myStr, 2)
Next myRow
End If
Next myArea
Application.DisplayAlerts = True

End Sub

 

[Ken Johnson]

Hi Richard,
try this out...

Public Sub CutPaste_ColB_into_ColA()
Application.ScreenUpdating = False
Dim iRows As Long
Dim iColumns As Long
Dim vaMergeArray As Variant
Dim I As Long
vaMergeArray = Selection
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
If iColumns <> 2 Then Exit Sub
For I = 1 To iRows
vaMergeArray(I, 1) = vaMergeArray(I, 1) & Space(1) &
vaMergeArray(I, 2)
vaMergeArray(I, 2) = ""
Next I
Selection = vaMergeArray
Selection.Columns.AutoFit
If Application.CountA(ActiveCell.Offset(0, 1).EntireColumn) = 0 Then
ActiveCell.Offset(0, 1).EntireColumn.Delete
End If
End Sub

I'm not sure if this is what you are after.

Make your selection first, then run the macro.
If you select more than two columns nothing happens.
If you select two columns then the values in the first column are
concatenated with the corresponding values in the second column along
with an intervening space.
The second column in the selection is emptied.
If this results in the entire second column being empty then that
column is deleted.
Ken Johnson

 

[Ken Johnson]

Hi Richard,

This is the same, I've just included a line break in the long line that
wrapped in the post, which causes a problem when you copy and paste the
code into Excel.

Public Sub CutPaste_ColB_into_ColA()
Application.ScreenUpdating = False
Dim iRows As Long
Dim iColumns As Long
Dim vaMergeArray As Variant
Dim I As Long
vaMergeArray = Selection
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
If iColumns <> 2 Then Exit Sub
For I = 1 To iRows
vaMergeArray(I, 1) = vaMergeArray(I, 1) _
& Space(1) & vaMergeArray(I, 2)
vaMergeArray(I, 2) = ""
Next I
Selection = vaMergeArray
Selection.Columns.AutoFit
If Application.CountA(ActiveCell.Offset(0, 1).EntireColumn) = 0 Then
ActiveCell.Offset(0, 1).EntireColumn.Delete
End If
End Sub

Ken Johnson

 

[Ken Johnson]

Hi Richard,
In line with Dave Peterson's post (14), which avoids the unwanted
trailing space when the second column contains a blank cell, I've added
a condition to deal with such a problem....

Public Sub CutPaste_ColB_into_ColA()
Application.ScreenUpdating = False
Dim iRows As Long
Dim iColumns As Long
Dim vaMergeArray As Variant
Dim I As Long
vaMergeArray = Selection
iRows = Selection.Rows.Count
iColumns = Selection.Columns.Count
If iColumns <> 2 Then Exit Sub
For I = 1 To iRows
If vaMergeArray(I, 2) <> "" Then
vaMergeArray(I, 1) = vaMergeArray(I, 1) _
& Space(1) & vaMergeArray(I, 2)
vaMergeArray(I, 2) = ""
End If
Next I
Selection = vaMergeArray
Selection.Columns.AutoFit
If Application.CountA(ActiveCell.Offset(0, 1).EntireColumn) = 0 Then
ActiveCell.Offset(0, 1).EntireColumn.Delete
End If
End Sub

Ken Johnson