excel - 将数组复制到过滤范围会产生不合理的结果

Question

将过滤范围的值复制到数组似乎没有问题:然后数组包含来自过滤和未过滤单元格的值。但是，当我将数组的内容复制回过滤范围时，结果对我来说是难以理解的。

这是我的代码:

Sub test()
    Dim rangecopy() As Variant

    rangecopy() = Range(Cells(2, 1), Cells(14, 3)).Value
    For c = LBound(rangecopy, 1) To UBound(rangecopy, 1)
        rangecopy(c, 1) = c
        rangecopy(c, 2) = c * 10
        rangecopy(c, 3) = c * 100
    Next
    Range(Cells(2, 1), Cells(14, 3)).Value = rangecopy()
End Sub

它应该给出以下结果。在这里，当宏将数组复制到该范围时，该范围未过滤。



如果范围按 D 列过滤(“NO”被过滤掉)，结果如下所示:



首先，过滤的单元格不会更新。然后，B 列中的大多数单元格从数组的第一列 (4, 5, 6) 中获取值，而其他一些单元格从数组的第二列 (10) 中正确获取值。最后两行填充了#N/A 错误。这应该这样工作吗？我正在使用 Office 2010。

Answer 1

我真的希望了解 VBA 内部工作原理的人可以对您的问题提供更多见解。我可以分享以下内容:

首先，它按预期工作。但是，我不知道为什么这是设计，也不知道分配过程中到底发生了什么。

有很多案例会产生类似的问题。例如，如果您打开过滤器(某些行被隐藏)并尝试向下填充(拖动)公式，您将看到类似的结果，因为隐藏的行没有被填充，但它们确实会影响(相对)引用在公式。另一方面，如果您手动复制并粘贴到过滤范围中，则数据将粘贴到隐藏行中(如您所愿)。

似乎作为自动过滤器范围一部分的任何引用范围实际上都是不连续的*。使用 Range.Address 并不总是揭示这一点，循环 Range.Areas 也不会。如果我们修改您的示例，我们可以看到“真正的”错误在哪里:

Dim r1 as range
Dim r2 as range

Set r1 = Sheet1.Range("A1:B5") 'some numbers in a range
Set r2 = Sheet2.Range("A2:B6") 'same-size range underneath a filtered header

r1.Copy Destination:=r2

它在所有行都可见时起作用。当 Sheet2 上的过滤器创建隐藏行时，结果是“运行时错误 '1004': ...复制区域和粘贴区域的大小和形状不同。”另一方面，使用“手动”/剪贴板方法适用于隐藏行:

r1.Copy    
r2.PasteSpecial (xlPasteValues)

由于将数组分配给一个范围会绕过剪贴板(如在第一个 block 中)，我们应该收到一个错误(而不是你最终得到错误的结果)。

我知道的唯一解决方案是遍历范围并为每个单元格分配一个值:

For i = 1 to LastRow
  For j = 1 to LastCol
    Sheet1.Cells(i,j).Value = myArr(i,j)
  Next
Next

或(更好)删除自动过滤器，将数组分配给范围，然后重新应用过滤器。

*从技术上讲，它是连续的，因此最好说该范围由多个范围/区域组成，尽管使用 .Address 并不表示这一点，并且当您尝试遍历 Range.Areas 时只有一个区域