Excel宏一次将一行连接到文件末尾 [英] Excel macro to concatenate one row at a time to end of file

问题描述

我需要一个Excel宏在每一行上联接七列数据，直到到达数据末尾.例如，如果我有一个这样的公式:

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:

=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

推荐答案

答案如此之多，我和我突出显示的那些无花果的重点已经浪费了:)

With so many answers, the main focus on what assylias and I were highlighting has gone to waste :)

但是，如果您仍然想要VBA答案.使用此方法.这比循环或自动填充要更快.

However, if you still want a VBA answer. Use this method. This is much faster than Looping or an Autofill.

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

如果您有1000行，则可能需要先关闭 Screenupdating 并将 Calculation 更改为"Manual"，然后再运行代码，然后在结束时重置它们代码.

If you have 1000's of rows then you might want to switch off Screenupdating and change Calculation to Manual before you run the code and then reset them at the end of the code.

这篇关于Excel宏一次将一行连接到文件末尾的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！