vba Excel宏一次将一行连接到文件末尾
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10318431/
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 macro to concatenate one row at a time to end of file
提问by user1356553
I need an Excel macro to join seven columns of data on each row until the end of the data is reached. For example if I have a formula like this:
我需要一个 Excel 宏来连接每行的七列数据，直到到达数据的末尾。例如，如果我有一个这样的公式：
=A1&B1&C1&D1&E1&F1&G1
How can I write the macro so that it increments for every row to the end of the file in a sequence like this?
我如何编写宏，以便它以这样的顺序为每一行递增到文件末尾？
=A1&B1&C1&D1&E1&F1&G1
=A2&B2&C2&D2&E2&F2&G2
=A3&B3&C3&D3&E3&F3&G3
回答by Siddharth Rout
With so many answers, the main focus on what assylias and I were highlighting has gone to waste :)
有这么多答案，我和 assylias 强调的主要焦点已经浪费了:)
However, if you still want a VBA answer. Use this method. This is much fasterthan Loopingor an Autofill.
但是，如果您仍然想要 VBA 答案。使用这种方法。这比或快得多。LoopingAutofill
Option Explicit
Sub Sample()
Dim LastRow As Long
Dim Ws As Worksheet
Set Ws = Sheets("Sheet1")
LastRow = Ws.Range("A" & Ws.Rows.Count).End(xlUp).Row
'~~> If your range doesn't have a header
Ws.Range("H1:H" & LastRow).Formula = "=A1&B1&C1&D1&E1&F1&G1"
'~~> If it does then
Ws.Range("H2:H" & LastRow).Formula = "=A2&B2&C2&D2&E2&F2&G2"
End Sub
If you have 1000's of rows then you might want to switch off Screenupdatingand change Calculationto Manual before you run the code and then reset them at the end of the code.
如果您有 1000 行，那么您可能希望在运行代码之前关闭Screenupdating并更改Calculation为手动，然后在代码末尾重置它们。
回答by Alistair Weir
I think the easiest way to do this would be to just fill down as assylias says but if you want to use VBA:
我认为最简单的方法是像 assylias 所说的那样填写，但如果你想使用 VBA：
Selection.AutoFill Destination:=Range("Your Fill Range"), Type:=xlFillDefault
Should copy across the other rows.
应该复制其他行。
回答by psubsee2003
I agree 100% with the comments and the other answers, why do you need VBA to do this, but just to answer your original question, this is how I would accomplish it:
我 100% 同意评论和其他答案，为什么您需要 VBA 来执行此操作，但只是为了回答您的原始问题，这就是我将如何完成它：
Sub FillAllWithFormula()
Dim i As Variant
Dim wsht As Worksheet
'If you are using this for a specific Worksheet use the following
Set wsht = ThisWorkbook.Worksheets(yourWorksheetName)
'or if you are always using this for the active sheet use the following
Set wsht = ActiveSheet
For i = 1 To wsht.Rows.Count
'Replace "X" with the column letter you want your formula to appear in
wsht.Range("X" & i).Formula = "=A" & i & "&B" & i & "&C" & i & "&D" & i & "&E" & i & "&F" & i & "&G" & i
Next
Set wsht = Nothing
End Sub
