Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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/comm...lic.excel.misc |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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. Rumpa Biswas wrote: 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/comm...lic.excel.misc -- Dave Peterson |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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 "Ken Johnson" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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. "Dave Peterson" wrote: 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. Rumpa Biswas wrote: 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/comm...lic.excel.misc -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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 FormatCellsAlignment, 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
Hi Ken,
Thanks for your reply. I will try the macro for sure. Wishing you too a very happy New Year. - Rumpa "Ken Johnson" wrote: 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 FormatCellsAlignment, 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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. Rumpa Biswas wrote: 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. "Dave Peterson" wrote: 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. Rumpa Biswas wrote: 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/comm...lic.excel.misc -- Dave Peterson -- Dave Peterson |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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 Dave Peterson wrote: 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. Rumpa Biswas wrote: 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. "Dave Peterson" wrote: 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. Rumpa Biswas wrote: 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/comm...lic.excel.misc -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
Another option would be to concatenate your cells into a single string. Regards JG -- pinmaster ------------------------------------------------------------------------ pinmaster's Profile: http://www.excelforum.com/member.php...fo&userid=6261 View this thread: http://www.excelforum.com/showthread...hreadid=496332 |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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 -- When we die, we leave all that we have... and take with us all that we are. Ravi Zacharias "Ken Johnson" wrote: 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 FormatCellsAlignment, 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 |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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 Dave Peterson wrote: 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 Dave Peterson wrote: 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. Rumpa Biswas wrote: 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. "Dave Peterson" wrote: 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. Rumpa Biswas wrote: 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/comm...lic.excel.misc -- Dave Peterson -- Dave Peterson -- Dave Peterson -- Dave Peterson |
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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 |
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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 |
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel should merge cells without removing content of any cell
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TRYING TO SET UP EXCEL SPREADSHEET ON MY COMPUTER | New Users to Excel | |||
Suggestion to add ability to merge non-adjacent cells in Excel. | Excel Discussion (Misc queries) | |||
How do I export comments into another excel column as cell content | Excel Worksheet Functions | |||
How do I clear cell content when excel document is closed? | Excel Worksheet Functions | |||
How do I merge cells in Excel, like just 2 cells to make one big . | Excel Discussion (Misc queries) |