vba 将单元格范围与列中的条件连接起来
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10749323/
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 range of cells with criteria in a column
提问by SeanC
I need help with vba concatenate cells.
我需要有关 vba 连接单元格的帮助。
I have a spread sheet: column A contain values like Product A for a few rows, Product B for a few rows, and so on. I want to concatenate, say cells in columns B-J for Product A, write the value to column K or another sheet, then do the same thing with Product B, and so on till the end of the spreadsheet.
我有一个电子表格：A 列包含一些值，例如几行的产品 A，几行的产品 B，等等。我想连接，比如说 BJ 列中产品 A 的单元格，将值写入 K 列或其他工作表，然后对产品 B 执行相同的操作，依此类推，直到电子表格结束。
I think this would need some vba coding which I am learning but not good yet to do the job. Please help!
我认为这需要一些我正在学习的 vba 编码，但还不能很好地完成这项工作。请帮忙！
Thank you, cj
谢谢你，cj
回答by Siddharth Rout
I think this would need some vba coding
我认为这需要一些 vba 编码
No you don't need vba for this :)
不，你不需要vba :)
Put this formula in K1 and drag it down
把这个公式放在K1里拖下来
=B1&" "&C1&" "&D1&" "&E1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1
This will concatenate the data with SPACEas a delimiter. If you do not want space then amend the above formula to
这将连接数据SPACE作为分隔符。如果您不想要空间，则将上述公式修改为
=B1&C1&D1&E1&F1&G1&H1&I1&J1
Similarly if you want to concatenate with COMMAas a delimiter then use this
同样，如果您想连接 withCOMMA作为分隔符，请使用此
=B1&", "&C1&", "&D1&", "&E1&", "&F1&", "&G1&", "&H1&", "&I1&", "&J1
and so on...
等等...
回答by SeanC
I read it as you want everything in a single column into another cell. This is a routine that will take all the data from the cell you specify, and concatenate everything going down until there is a break in the data
我读它是因为您希望将一列中的所有内容放入另一个单元格中。这是一个例程，它将从您指定的单元格中获取所有数据，并将所有内容串联起来，直到数据出现中断
Option Explicit
Function ColConc(CellRef As Range, Delimiter As String)
Dim LoopVar As Long
Dim StartRow As Long
Dim EndRow As Long
Dim Concat As String
Dim Col As Long
Col = CellRef.column
StartRow = CellRef.Row
EndRow = CellRef.End(xlDown).Row
Concat = ""
For LoopVar = StartRow To EndRow
Concat = Concat & Cells(LoopVar, Col).Value
If LoopVar <> EndRow Then Concat = Concat & Delimiter
Next LoopVar
ColConc = Concat
End Function
call by using the formula =ColConc(A2," ")and this will get everything from cell A2, down to the end of that column, with a space as the delimiter.
The delimiter can be any string, so you can place anything between the data. A blank cell ends the data it uses for concatenating into a single string.
It will only work on the current sheet - more coding would be needed to get that part working
使用公式调用=ColConc(A2," ")，这将获得从单元格 A2 到该列末尾的所有内容，并以空格作为分隔符。
分隔符可以是任何字符串，因此您可以在数据之间放置任何内容。一个空白单元格结束它用于连接成单个字符串的数据。
它仅适用于当前工作表 - 需要更多编码才能使该部分工作
