Data corruption using VBA array function on filtered data

user_15067551 0
Viewed 111

Take a very simple spreadsheet

enter image description here

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

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

enter image description here

Run the code again and you get a mess:

enter image description here

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 excel
1 Answers

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

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
user_47225770
Related