将两个单元格内容连接为一个的 Excel VBA 脚本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14887448/
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
Excel VBA script to concatenate two cell contents into one
提问by Selvam
Sub Concat()
Do While ActiveCell <> ""
'The rows are filtered to display only "London"
'The changes required are for "London" only
If ActiveCell.Offset(0,0) = "London" Then
ActiveCell.Offset(0, 0).FormulaR1C1 = _
ActiveCell.Offset(0, 0) & " " & ActiveCell.Offset(0, 1)
ActiveCell.Offset(0, 1) = ""
End If
ActiveCell.Offset(1, 0).Select
Loop
End Sub
I have close to 13500 rows. This doesn't work. I can't see any noticeable changes made.
我有接近 13500 行。这不起作用。我看不到任何明显的变化。
回答by Tim Williams
Sub Concat()
Do While ActiveCell <> ""
With ActiveCell
If .Value = "London" Then
.Value = .Value & .Offset(0, 1).Value
.Offset(0, 1).Value = ""
End If
End With
ActiveCell.Offset(1, 0).Select
Loop
End Sub
回答by Glenn Stevens
You can write a loop using a for eachstatement to loop through all the rows in the worksheet's UsedRange. For each row, check to see if the value in the first column of the row is "London" and if so overwrite the contents of the column with the combination of column1 & column2.
您可以使用for each语句编写循环以遍历工作表的 UsedRange 中的所有行。对于每一行，检查该行第一列中的值是否为“London”，如果是，则使用 column1 和 column2 的组合覆盖该列的内容。
Then simply copy the value from column3 into column2 and clear column3's value using ClearContents:
然后简单地将 column3 中的值复制到 column2 并使用ClearContents以下命令清除 column3 的值：
Sub Concat()
'Loop through all the rows
For Each Row In ActiveSheet.UsedRange.Rows
'Look at the cell in the first column of the current row
'If value is "London" concatinate column 1 and 2 and store in column1
'Note, the check for "London" is an exact, case sensative check!
If Row.Columns(1) = "London" Then
Row.Columns(1) = Row.Columns(1) & " " & Row.Columns(2)
Row.Columns(2) = Row.Columns(3)
Row.Columns(3).ClearContents
End If
Next Row
End Sub
