VBA宏提速：跨工作表整列引用赋值问题求助

Hi there, let's fix that slow macro and get your data moving efficiently! The issue with your original approach is twofold: you're transferring entire columns (which includes thousands of empty cells) and multi-range assignments can have unexpected mapping behavior in VBA. Here's an optimized solution that'll cut your runtime drastically:

Key Optimizations to Speed Up Your Macro #

• Work with only valid data rows: Avoid transferring empty rows by finding the last used row in your source columns.
• Use arrays for bulk operations: Reading/writing data to arrays is way faster than interacting with cells directly (VBA minimizes worksheet access, which is a major speed bottleneck).
• Disable background processes: Turn off screen updates, events, and automatic calculations during the macro—these eat up unnecessary resources.

Complete Working Code #

Assuming B is your target worksheet index (e.g., 31 for the 31st sheet), here's the code:

Sub TransferDataQuickly()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim lastRow As Long
    Dim arrColA, arrColB, arrColJ, arrColL
    
    ' Enable speed optimizations
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    
    ' Set up your worksheets (using index for target)
    Set wsSource = ThisWorkbook.Sheets("Sheetx")
    Set wsTarget = ThisWorkbook.Sheets(B) ' Replace B with your actual index, e.g., 31
    
    ' Find the last row with data across all source columns (avoid empty rows)
    lastRow = Application.Max( _
        wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row, _
        wsSource.Cells(wsSource.Rows.Count, "B").End(xlUp).Row, _
        wsSource.Cells(wsSource.Rows.Count, "J").End(xlUp).Row, _
        wsSource.Cells(wsSource.Rows.Count, "L").End(xlUp).Row _
    )
    
    ' Exit early if there's no data to transfer
    If lastRow < 1 Then
        GoTo Cleanup
    End If
    
    ' Read source data into arrays (single read operation per column)
    arrColA = wsSource.Range("A1:A" & lastRow).Value
    arrColB = wsSource.Range("B1:B" & lastRow).Value
    arrColJ = wsSource.Range("J1:J" & lastRow).Value
    arrColL = wsSource.Range("L1:L" & lastRow).Value
    
    ' Write arrays to target columns (single write operation per column)
    wsTarget.Range("A6:A" & 5 + lastRow).Value = arrColA
    wsTarget.Range("B6:B" & 5 + lastRow).Value = arrColB
    wsTarget.Range("C6:C" & 5 + lastRow).Value = arrColJ
    wsTarget.Range("D6:D" & 5 + lastRow).Value = arrColL
    
Cleanup:
    ' Restore Excel's default settings
    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
End Sub

Why This Works Better Than Your Original Code #

1. No more full-column transfers: By using lastRow, we only process rows that actually have data—this eliminates transferring 100k+ empty cells which was killing your runtime.
2. Array-based operations: Reading data into memory (arrays) and then writing it all at once cuts down on the slow back-and-forth between VBA and the worksheet.
3. Controlled worksheet references: Using ThisWorkbook ensures you're referencing the correct file, and explicit worksheet variables make the code clearer and less error-prone.

Quick Notes #

• If your source columns might have different last rows, using Application.Max ensures we capture all data from the longest column.
• The Cleanup section is critical—always restore Excel's settings even if the macro hits an error (you could add On Error GoTo Cleanup if you want extra robustness).

内容的提问来源于stack exchange，提问作者PabloCasts