vba 使用宏连接列 D 和 C
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17999102/
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
Concatenate columns D and C using macro
提问by user2642842
Could you please help in concatenating cells using Excel 2010 macro. I have columns A to E. I want to concatenate columns D and C in column F. Please note that I don't know the exact number of rows in column D and C but the macro should stop concatenating when there are no values in the said columns. Sample:
您能否帮助使用 Excel 2010 宏连接单元格。我有 A 列到 E 列。我想连接 F 列中的 D 列和 C 列。请注意，我不知道 D 列和 C 列中的确切行数，但是当没有值时，宏应该停止连接列说。样本：
A B C D E F
0 Exist Echalas Gerald 25256 Gerald Echalas
....
....
Thank you.
谢谢你。
回答by Elias
Try this out, from MSDN, it has an example for going through each row.
试试这个，来自 MSDN，它有一个遍历每一行的例子。
http://support.microsoft.com/kb/213477
http://support.microsoft.com/kb/213477
Their sample:
他们的样本：
Sub ConcatColumns()
Do While ActiveCell <> "" 'Loops until the active cell is blank.
'The "&" must have a space on both sides or it will be
'treated as a variable type of long integer.
ActiveCell.Offset(0, 1).FormulaR1C1 = _
ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0)
ActiveCell.Offset(1, 0).Select
Loop
End Sub
回答by RLY
Something like this (untested)
像这样的东西（未经测试）
dim row as integer
row = 1
while(cells(row, 3) <> "" and cells(row, 4) <> ""
cells(row, 5) = cells(row, 3) & " " & and cells(row, 4)
row = row + 1
wend
回答by PatricK
This will combine values columns C, D to F as "D, C" regardless of gaps up to the last used row.
这会将值列 C、D 到 F 组合为“D、C”，而不管到最后使用的行的间隙如何。
Sub CombineCols()
Dim oWS As Worksheet, lLastRow As Long, r As Long
Set oWS = ActiveSheet
lLastRow = oWS.Cells.SpecialCells(xlLastCell).Row
For r = 1 To lLastRow
' Combine if both C and D are not empty
If Len(oWS.Cells(r, 3)) > 0 And Len(oWS.Cells(r, 4)) > 0 Then
oWS.Cells(r, 6).Value = oWS.Cells(r, 4).Value & " " & oWS.Cells(r, 3).Value
End If
Next
End Sub
You can change the check condition so that it does not require both valid text is columns C and D to combine.
您可以更改检查条件，以便它不需要合并 C 列和 D 列的有效文本。
Tip: You can reference the column of a cell with long number - starting from A=1.
提示：您可以使用长数字引用单元格的列 - 从 A=1 开始。
回答by Sathish Kothandam
Lets say u r in sheet1
让我们在 sheet1 中说 ur
Sub test()
Dim r As Range
Dim lr As Integer
With Sheets("Sheet1")
lr = .Range("C500000").End(xlUp).Row
For Each r In .Range("F2:F" & lr)
r.Value = r.Offset(0, -2).Value & " " & r.Offset(0, -3).Value
Next r
End With
End Sub
Tested
已测试
回答by Dick Kusleika
Seems like a good job for a formula
似乎是一个很好的公式工作
Sub ConcatName()
With ActiveSheet
.Range("F1").Resize(.Range("A1").CurrentRegion.Rows.Count, 1).FormulaR1C1 = "=RC[-2]&"" ""&RC[-3]"
End With
End Sub
The CurrentRegion property gets the blob of data around A1, extends F1 by the number of rows, then inserts a formula that concatenates.
CurrentRegion 属性获取 A1 周围的数据块，将 F1 扩展行数，然后插入一个连接公式。
回答by Nick
The concatenate function is quite easy to implement in a Macro. All you do is place an '&' between objects you would like to concatenate. In your case is would look like this:
连接函数在宏中很容易实现。您所做的就是在要连接的对象之间放置一个“&”。在你的情况下是这样的：
Range("F1").Value = Range("C1").Value & " " & Range("D1")
*Note that I added a space between the C1 and D1 values
*请注意，我在 C1 和 D1 值之间添加了一个空格
Next you are going to need a loop to iterate through your data. There are several good methods for controlling when the loop will stop.
接下来，您将需要一个循环来遍历您的数据。有几种很好的方法可以控制循环何时停止。
Method #1
方法#1
The simplest method is to not worry about when the macro stops. This may work in your case be concatenating nothing with nothing yields nothing.
最简单的方法是不用担心宏何时停止。这可能适用于您的情况，将任何内容与任何内容连接起来都不会产生任何结果。
loop
Range("F1").Value = Range("C1").Value & " " & Range("D1")
next
Method #2
方法#2
End the loop when a specified column runs out of data. In your case this could look like:
当指定的列用完数据时结束循环。在您的情况下，这可能如下所示：
While(Range("A" & i).value <> "")
Range("F1").Value = Range("C1").Value & " " & Range("D1")
i = i + 1
Wend
Method #3
方法#3
Excel has a function (.count) that will return the number of entries in a column (will not work correctly if there are gaps). Use the value return along with a for loop to stop the loop when there is no more data.
Excel 有一个函数 (.count)，它将返回列中的条目数（如果存在间隙，将无法正常工作）。当没有更多数据时，使用返回值和 for 循环来停止循环。
