在过滤的数据上使用VBA数组功能进行数据损坏 [英] Data corruption using VBA array function on filtered data

问题描述

制作一个非常简单的电子表格

Take a very simple spreadsheet

在其上运行以下代码.它只是将工作表复制到数组，然后再次返回.它对工作表没有任何影响.

Run the following code on it. It just copies the sheet to an array and then back again. It should not make any difference to the sheet.

Option Explicit

Sub Test()
    Dim vArr As Variant
    
    ' Copy the contents of the sheet to a 2D variant array
    vArr = Sheets("Sheet1").Cells(1, 1).CurrentRegion
    
    ' Copy the variant array back to the sheet
    Sheets("Sheet1").Cells(1, 1).Resize(UBound(vArr, 1), UBound(vArr, 2)) = vArr
    
    ' There should be no difference to the sheet
End Sub

一切正常.现在，对工作表进行过滤，并排除第2行(或其他任何行)

All works fine. Now filter the sheet and exclude row 2 (or indeed any other row)

再次运行代码，您会一团糟:

Run the code again and you get a mess:

这是一个错误，还是这里有一些更深层次的理念在起作用?是否有办法解决这个问题，而无需清除过滤器.

Is this a bug or is there some deeper philosophy at work here? Is there a way round this, short of clearing the filter.

更新我还要指出的是，隐藏行和过滤后的行之间是有区别的.如果您隐藏一行，则代码可以正常工作.似乎只与过滤有关.

UPDATE I would also note that there is a difference between hidden rows and filtered rows. If you hide a row then the code works fine. It seems to be only to do with filtering.

推荐答案

如果将VBA脚本更改为用于循环，则可以解决此问题.

If you change your VBA script to use for loops you can get around this issue.

稍微修改您的VBA脚本，我没有看到您上面提到的问题.

Slightly modifying your VBA script I did not see the issue you mentioned above.

Option Explicit

Sub Test()
    Dim vArr As Variant
    
    ' Copy the contents of the sheet to a 2D variant array
    vArr = Sheets("Sheet1").Cells(1, 1).CurrentRegion


    ' Copy the variant array back to the sheet
    'Sheets("Sheet1").Cells(1, 1).Resize(UBound(vArr, 1), UBound(vArr, 2)) = vArr

    Dim row As Integer
    Dim col As Integer
    
    For row = 1 To UBound(vArr, 1)
    
        For col = 1 To UBound(vArr, 2) 
            'Debug.Print vArr(row, col)
            Sheets("Sheet1").Cells(row, col) = vArr(row, col)
        Next col
    Next row
    ' There should be no difference to the sheet
End Sub

这篇关于在过滤的数据上使用VBA数组功能进行数据损坏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！