vba - Excel VBA 优化 - 转置数据

Question

我收到了一份以 Excel 形式汇总的报告，我需要将其展开以便将其导入 Access。这是该行的示例:

需要发生的是客户帐户和名称需要调换到与优惠券行相邻的位置，并且需要进行复制，以便每个优惠券行都有此信息。转换后，数据应如下所示:

Customer Account |  Name  | Date | Voucher | Invoice | Transation Text | Currency

请注意，以“USD”开头的行表示该客户的记录结束。

我已经成功实现了以下代码:

Sub Process_Transactions()
'turn off some Excel functionality so code runs faster
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = False
Application.EnableEvents = False 

Dim i As Long
For i = 1 To 731055

    'Move two columns in
    ActiveCell.Offset(0, 2).Select

    'Select the customer account and name
    Range(ActiveCell, ActiveCell.Offset(1, 1)).Select

    'Copy and paste it down two rows and over two columns
    Selection.Cut
    ActiveCell.Offset(2, -2).Select
    ActiveSheet.Paste

    'Hop up a couple rows and delete 3 rows before the data that are not useful
    Rows(ActiveCell.Offset(-2).Row).Select
    Selection.Delete Shift:=xlUp
    Selection.Delete Shift:=xlUp
    Selection.Delete Shift:=xlUp

    'Select the next row
    Rows(ActiveCell.Offset(1).Row).Select

    'If the first record in the row is not "USD", then we have multiple rows for
    'this customer
    While (ActiveCell.Offset(0, 2) <> "USD")
        'Copy and Paste the customer account and number for each
        'transaction row
        ActiveCell.Select
        Range(ActiveCell.Offset(-1, 0), ActiveCell.Offset(-1, 1)).Select
        Selection.Copy
        ActiveCell.Offset(1, 0).Select
        ActiveSheet.Paste
        ActiveCell.Select
        ActiveCell.Offset(1, 0).Select
    Wend

    'Delete the two rows after the data that we need
    ActiveCell.Select
    Rows(ActiveCell.Row).Select
    Selection.Delete Shift:=xlUp
    ActiveCell.Select
    Rows(ActiveCell.Row).Select
    Selection.Delete Shift:=xlUp

    'Move to the next row to start over
    ActiveCell.Select
    Debug.Print "Current Row: " & i
Next i

'at the end, don't forget to restore the default behavior
'calculate the formulas
Application.Calculate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.DisplayStatusBar = True
Application.EnableEvents = True
End Sub

问题是程序非常慢。昨晚我让代码运行了大约 10 个小时，它只处理了 33k。我有大约 150 万条记录需要处理。

我意识到我正在使用的技术实际上是在移动事件单元格，因此删除它可能会有所帮助。但是，我不确定如何继续。如果这是一个失败的原因并且更适合 .net 实现，请随时提出建议。

Answer 1

您的代码中挤满了 Excel-VBA 方法，效率非常低!我来拍几张:

不要使用.Select和Selection.。那是 super 慢。

为什么要这样做

Range(ActiveCell, ActiveCell.Offset(1, 1)).Select
Selection.Cut

什么时候你可以做到这一点

Range(ActiveCell, ActiveCell.Offset(1, 1)).Cut

另外，请勿使用 ActiveCell 在工作表中移动。只需直接对您需要的任何单元格或行进行操作即可，例如

Sheet1.Cells(i,2).Copy
Sheet1.Cells(i,1).Paste

实际上，完全避免复制/粘贴，只需说

Sheet1.Cells(i,1).Value = Sheet1.Cells(i,2).Value

避免多次引用同一个对象，而使用 With 代替。这里，Sheet1 使用了两次，所以你可以这样写:

With Sheet1
    .Cells(i,1).Value = .Cells(i,2).Value
End With

以上只是一些示例，您必须根据自己的情况进行调整，还有更多需要优化的地方，但它们可以帮助您入门。清理完代码后，请向我们展示您的代码，我们会提供更多建议!