vba 用宏在excel中将多个单元格合并为一个？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2089756/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Combine multiple cells into one in excel with macro?
提问by CCID
I have a similar question to this one:
我有一个类似的问题：
Merge the contents of 2 cells into another 3rd cell using VBA in Excel
使用 Excel 中的 VBA 将 2 个单元格的内容合并到另一个第 3 个单元格中
But I want to combine a range of cells within a column, eg A2:A50. Sometimes I have over 300 cells to be combined into one. Values are text. Is there any way to modify this macro so that it works on a range instead of just two cells?
但我想在一列中组合一系列单元格，例如 A2:A50。有时我有超过 300 个单元格要合并为一个单元格。值是文本。有什么方法可以修改这个宏，使其在一个范围内工作，而不仅仅是两个单元格？
Thanks!
谢谢！
采纳答案by Mike Rosenblum
Based on the thread you are citing, I guess you wish to return the concatination of all the values held by the cells, interpreting all the values as strings?
根据您引用的线程，我猜您希望返回单元格持有的所有值的连接，将所有值解释为字符串？
For that, you could use a VBA macro that looks like this:
为此，您可以使用如下所示的 VBA 宏：
Function ConcatinateAllCellValuesInRange(sourceRange As Excel.Range) As String
Dim finalValue As String
Dim cell As Excel.Range
For Each cell In sourceRange.Cells
finalValue = finalValue + CStr(cell.Value)
Next cell
ConcatinateAllCellValuesInRange = finalValue
End Function
As an example, you could call it like this:
例如，您可以这样称呼它：
Sub MyMacro()
MsgBox ConcatinateAllCellValuesInRange([A1:C3])
End Sub
Is this what you were looking for?
这就是你要找的吗？
Mike
麦克风
回答by Paul McMahon
Try the following macro, not very elegant in that it doesn't do any error checking etc but works. Assign the macro to a button, click in a cell, click the macro button, highlight the desired (source) range to merge using your mouse (will autofill in range in the input box in the dialogue box), click ok, highlight the destination cell (will autofill the input box in the next dialogue box) click ok, all cells will be merged with a single space character into the destination cell, which can be in the original source range). Up to you to delete the superfluous cells manually. Workks with both rows and columns but not blocks.
试试下面的宏，不是很优雅，因为它不做任何错误检查等，但可以工作。将宏分配给按钮，单击单元格，单击宏按钮，使用鼠标突出显示要合并的所需（源）范围（将在对话框的输入框中自动填充范围），单击确定，突出显示目标单元格（将在下一个对话框中自动填充输入框）单击确定，所有单元格将与单个空格字符合并到目标单元格中，目标单元格可以在原始源范围内）。由您手动删除多余的单元格。同时处理行和列但不处理块。
Sub JoinCells()
Set xJoinRange = Application.InputBox(prompt:="Highlight source cells to merge", Type:=8)
xSource = 0
xSource = xJoinRange.Rows.Count
xType = "rows"
If xSource = 1 Then
xSource = xJoinRange.Columns.Count
xType = "columns"
End If
Set xDestination = Application.InputBox(prompt:="Highlight destination cell", Type:=8)
If xType = "rows" Then
temp = xJoinRange.Rows(1).Value
For i = 2 To xSource
temp = temp & " " & xJoinRange.Rows(i).Value
Next i
Else
temp = xJoinRange.Columns(1).Value
For i = 2 To xSource
temp = temp & " " & xJoinRange.Columns(i).Value
Next i
End If
xDestination.Value = temp
End Sub
回答by HotSauceCoconuts
Just to add to Mike's solution, if you want to get your range from a variable instead of a defined range (I had trouble with the syntax):
只是为了添加到 Mike 的解决方案，如果您想从变量而不是定义的范围中获取范围（我在语法上遇到了问题）：
Sub MyMacro()
dim myVar As Range
MsgBox ConcatinateAllCellValuesInRange(myVar)
End Sub
