Excel 2010 VBA。从工作表连接 2 列并将它们粘贴到另一个
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19956515/
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 2010 VBA. Concatenate 2 columns from a Sheet and paste them in another
提问by Hyman Casas
Using Excel 2010, I'm trying to create a script that concatenates two text columns (A and B) from Sheet1 and pastes the result in column A of Sheet2.
使用 Excel 2010，我尝试创建一个脚本，该脚本连接来自 Sheet1 的两个文本列（A 和 B）并将结果粘贴到 Sheet2 的 A 列中。
The workbook uses an external datasource for loading both columns, so the number of rows is not fixed.
工作簿使用外部数据源加载两列，因此行数不固定。
I've tried the following code, but not working. variable lRow is not taking any value.
我试过下面的代码，但没有用。变量 lRow 没有取任何值。
Sub Concat()
Sheets("Sheet1").Select
Dim lRow As Long
lRow = Range("A" & Rows.count).End(xlUp).Row
For i = 2 To lRow
ActiveWorkbook.Sheets("Sheet2").Cells(i, 1) = Cells(i, 1) & Cells(i, 2)
Next i
End Sub
What am I doing wrong. Thanks for helping!
我究竟做错了什么。感谢您的帮助！
回答by sancho.s ReinstateMonicaCellio
As to what are you doing wrong, I suggest you use
至于你做错了什么，我建议你使用
Sub Concat()
Sheets("Sheet1").Select
Dim lRow As Long, i As Long
Dim rng As Range
Set rng = Range("A" & Rows.Count).End(xlUp)
Debug.Print rng.Address(External:=True)
lRow = rng.Row
For i = 2 To lRow
ActiveWorkbook.Sheets("Sheet2").Cells(i, 1) = Cells(i, 1) & Cells(i, 2)
Next i
End Sub
to see what is going on. I tried exactly what you used and it worked for me (Excel 2010).
看看发生了什么。我完全尝试了您使用的方法，并且对我有用（Excel 2010）。
Specifying what does "variable lRow is not taking any value" mean would help.
指定“变量 lRow 不取任何值”的含义会有所帮助。
You could also try alternatively
你也可以试试
Sub Concat2()
Sheets("Sheet1").Select
Dim lRow As Long, i As Long
Dim rng As Range
Set rng = Range("A2").End(xlDown)
Debug.Print rng.Address(External:=True)
lRow = rng.Row
For i = 2 To lRow
ActiveWorkbook.Sheets("Sheet2").Cells(i, 1) = Cells(i, 1) & Cells(i, 2)
Next i
End Sub
which should give the same result if yo do not have blank cells in the middle of the source column A.
如果源列 A 的中间没有空白单元格，这应该给出相同的结果。
回答by sancho.s ReinstateMonicaCellio
I would advise getting out of the .Selectmethod of XL VBA programming in favor of direct addressing that will not leave you hanging with errors.
我建议放弃.SelectXL VBA 编程的方法，转而使用不会让您陷入错误的直接寻址。
Sub Concat()
Dim i As Long, lRow As Long
With Sheets("Sheet1")
lRow = .Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To lRow
Sheets("Sheet2").Cells(i, 1) = .Cells(i, 1) & .Cells(i, 2)
Next i
End With
End Sub
Note the periods (aka . or full stop) that prefix .Cellsand .Range. These tell .Cells and .Range that they belong to the worksheet referenced in the With ... End Withblock; in this example that would be Sheets("Sheet1").
注意时段（亦称或完全停止），其前缀.Cells和.Range。这些告诉 .Cells 和 .Range 它们属于With ... End With块中引用的工作表；在这个例子中，将是Sheets("Sheet1").
If you have a lot of rows to string together you would be better off creating an array of the values from Sheet1 and processing the concatenation in memory. Split off the concatenated values and return them to Sheet2.
如果你有很多行要串在一起，你最好从 Sheet1 创建一个值数组并在内存中处理连接。拆分连接的值并将它们返回到 Sheet2。
Sub concat2()
Dim c As Long, rws As Long, vCOLab As Variant
With Sheets("Sheet1")
rws = .Range("A2:A" & .Cells(Rows.Count, 1).End(xlUp).Row).Rows.Count
vCOLab = .Range("A2").Resize(rws, 3)
For c = LBound(vCOLab, 1) To UBound(vCOLab, 1)
'Debug.Print vCOLab(c, 1) & vCOLab(c, 2)
vCOLab(c, 3) = vCOLab(c, 1) & vCOLab(c, 2)
Next c
End With
Sheets("Sheet2").Range("A2").Resize(rws, 1) = Application.Index(vCOLab, , 3)
End Sub
When interacting with a worksheet, bulk operations will beat a loop every time; the only question is by how much.
与工作表交互时，批量操作每次都会循环；唯一的问题是多少。
