在Excel VBA中复制大量值的最快方法 [英] Fastest Method to Copy Large Number of Values in Excel VBA

问题描述

很简单，我想知道将单元格值从一张纸复制到另一张纸的最快方法是什么.

Quite simply, I am wondering what the fastest method of copying cell values from one sheet to another is.

通常，我将按列和/或行遍历单元格，并使用如下一行:

Generally, I'll loop through the cells by column and/or row and use a line such as:

Worksheets("Sheet1").Cells(i,j).Value = Worksheets("Sheet1").Cells(y,z).Value

在其他情况下，如果我的范围不是连续的行/列(例如，我想避免覆盖已经包含数据的单元格)，则将在循环内添加一个条件，或者使用行和我要循环遍历的列号，然后循环遍历数组元素.例如:

In other cases where my ranges are not consecutive rows/columns (e.g. I want to avoid overwriting cells that already contain data) I'll either have a conditional inside the loop, or I'll fill an array(s) with row & column numbers that I want to cycle through, and then cycle through the array elements. For example:

Worksheets("Sheet1").Cells(row1(i),col1(j)).Value = Worksheets("Sheet2").Cells(row2(y),col2(z)).Value

使用我要复制的单元格和目标单元格来定义范围，然后执行Range.Copy和Range.Paste操作会更快吗?是否可以使用数组来定义范围，而不必循环遍历它?还是循环遍历数组以定义一个范围然后复制粘贴该范围而不是通过循环等价单元格值会更快吗?

Would it be faster to define ranges using the cells I want to copy and the destination cells, then do a Range.Copy and Range.Paste operation? Is it possible to define a range using an array without having to loop through it anyway? Or will it be faster anyway to loop through an array to define a range and then copy-pasting the range instead of equating the cell values by looping?

我觉得根本不可能复制和粘贴这样的范围(即它们需要是通过矩形数组连续并粘贴到相同大小的矩形数组中的单元格).话虽这么说，我认为有可能在不循环遍历每个单元并等价这些值的情况下等价于两个范围的元素.

I feel like it might not be possible to copy and paste ranges like this at all (i.e. they would need to be cells continuous through a rectangular array and pasted into a rectangular array of the same size). That being said, I would think that it's possible to equate the elements of two ranges without looping through each cell and equating the values.

推荐答案

对于矩形块，这是

Sub qwerty()
    Dim r1 As Range, r2 As Range

    Set r1 = Sheets("Sheet1").Range("A1:Z1000")
    Set r2 = Sheets("Sheet2").Range("A1")

    r1.Copy r2
End Sub

很快.

对于活动表上的非连续范围，我将使用循环:

For a non-contiguous range on the activesheet, I would use a loop:

Sub qwerty2()
    Dim r1 As Range, r2 As Range

    For Each r1 In Selection
        r1.Copy Sheets("Sheet2").Range(r1.Address)
    Next r1
End Sub

EDIT#1:

范围到范围方法甚至不需要中间数组:

The range-to-range method does not even require an intermediate array:

Sub ytrewq()
    Dim r1 As Range, r2 As Range

    Set r1 = Sheets("Sheet1").Range("A1:Z1000")
    Set r2 = Sheets("Sheet2").Range("A1:Z1000")

    r2 = r1
End Sub

这实际上与以下内容相同:

this is really the same as:

ary=r1.Value
r2.value=ary

，除了ary是隐式的.

这篇关于在Excel VBA中复制大量值的最快方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！