Geek asks and answers

Copying an array to a filtered range gives irrational results

Copying the values ​​of the filtered range to the array seems to be no problem: the array then contains values ​​from both filtered and unfiltered cells. However, when I copy the contents of the array back to the filtered range, the results are not clear to me.

Here is my code:

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

It is supposed to give the following result. Here the range was unfiltered when the macro copied the array.

Imgur

If the range is filtered by column D ("NO" is filtered out), the result is as follows:

Imgur

. B (4, 5, 6), (10). # N/A. ? Office 2010.

+3
1

, -, VBA, . :

-, . , , .

, . , ( ) () , , , () . , , ( ).

, , , Autofilter, *. 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)

( ), ( ).

, , - , :

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

() Autofilter, , .

* , , /, .Address , , Range.Areas

+1

More articles:


All Articles