时间戳

首页 > 解决方案 > Is there a faster way to copy-paste dynamic ranges in VBA?

excel - Is there a faster way to copy-paste dynamic ranges in VBA?

问题描述

I am trying to merge daily report to a single main document. Before, I was using Excel functions to do this. But the file size became enormous. So after all I thought, I may use VBA to eliminate huge amount of functions on the worksheets and reduce file size and calculation time as well.

Basically I have main line numbers as database on Sheet1 (main document). From Sheet2 I need to take daily amounts of works done for the line numbers listed on Sheet1.

For example, in Sheet1, cell "M3" represents the first line number (Line-01). In cell "X3", there will be work amount (10 joints) which comes from Sheet2 Range "O:O". This will repeat at random times according to daily performance. So I would have datas from range "O3" to "O1000" (even more). 

Sub DailyProgress()
    Dim i As Long

    Sheets(1).Activate

    Range("X3:X" & Range("M" & Rows.Count).End(xlUp).row).Formula = _
    "=IFERROR(vlookup(M3,Sheet2!D:O,12,0),Text(,))"

    i = 3
    'Because my first cell is on row 3
    Do Until IsEmpty(Cells(i, 12))        
        Cells(i, 24).Copy
        Cells(i, 24).PasteSpecial xlPasteValues

        i = i + 1
    Loop
End Sub

I wrote the code to make vlookup, copy and paste as value. But this block is not smooth enough. By the way, I need to copy datas to cells "Y3" and "Z3" from Sheet2. So It will take much more time next time.

标签： excelvbaloops

解决方案

请注意，您可以使用Cells(i, 24).Value = Cells(i, 24).Value更快的而不是复制/粘贴。此外，如果您不是对每个单元格执行此操作，而是一次针对整个范围执行此操作，则会快得多：Range("X3", "X" & LastRow).Value = Range("X3", "X" & LastRow).Value

由于您已经确定了LastRow编写公式的方法，您可以使用相同的方法将其转换为一个值（没有循环）。

Sub DailyProgress()
    With Sheets(1).Range("X3:X" & .Range("M" & Rows.Count).End(xlUp).Row)
        .Formula = "=IFERROR(vlookup(M3,Sheet2!D:O,12,0),Text(,))"
        .Value = .Value
    End With
End Sub

推荐阅读