Variant Array affected by AutoFilter?

[David McCulloch]

Can variant arrays be adversely affected by Excel's Autofilter?


When Excel's Autofilter is NOT enabled, the following code seems to work
correctly. When Excel's Autofilter is enabled and rows are being filtered,
the following code does NOT work correctly (specifically and as best that I
currently understand it, some of the cells, at least those that are not
filtered, are not loaded with the correct values).

Dim My_Array As Variant
My_Array = _
wsSheet.UsedRange.Columns(X_MY_COLUMN)
'
' (change some values of My_Array)
'
wsSheet.UsedRange.Columns(X_MY_COLUMN) = _
My_Array


If I write the array back to the spreadsheet cell by cell, it works fine.
Here's the code that works, even when rows are filtered:

Dim My_Array As Variant
My_Array = _
wsSheet.UsedRange.Columns(X_MY_COLUMN)
'
' (change some values of My_Array)
'
With wsSheet
For i = X_DATA_ROW To UBound(My_Array, 1)
.Cells(i, X_MY_COLUMN) = My_Array(i, 1)
Next i
End With


Dave

 

[Dave Peterson]

Yep.

And that's the workaround I'd use.

Can variant arrays be adversely affected by Excel's Autofilter?

When Excel's Autofilter is NOT enabled, the following code seems to work
correctly. When Excel's Autofilter is enabled and rows are being filtered,
the following code does NOT work correctly (specifically and as best that I
currently understand it, some of the cells, at least those that are not
filtered, are not loaded with the correct values).

Dim My_Array As Variant
My_Array = _
wsSheet.UsedRange.Columns(X_MY_COLUMN)
'
' (change some values of My_Array)
'
wsSheet.UsedRange.Columns(X_MY_COLUMN) = _
My_Array

If I write the array back to the spreadsheet cell by cell, it works fine.
Here's the code that works, even when rows are filtered:

Dim My_Array As Variant
My_Array = _
wsSheet.UsedRange.Columns(X_MY_COLUMN)
'
' (change some values of My_Array)
'
With wsSheet
For i = X_DATA_ROW To UBound(My_Array, 1)
.Cells(i, X_MY_COLUMN) = My_Array(i, 1)
Next i
End With

Dave