Excel cell fill range after filter applied
I want to apply a filter to a dataset, after which I would like to fill a range of data into a series of cells. But now, if this is done, the last two rows will get the values of the first cell.
-74.4398
-74.2028
-69.8689
-73.1567
-80.1015
-75.822
-75.0529
-75.9859
-79.2546
-72.8093
-71.6604
This is a list of numbers in cells B3 - B13. One button with the following VBA code:
Private Sub CommandButton1_Click()
Dim arr() As Variant
Dim i As Integer
ReDim arr(1 To 11, 1 To 1)
arr(1, 1) = "Hallo"
arr(2, 1) = "Welt"
arr(3, 1) = "Holla"
arr(4, 1) = "verdugón"
arr(5, 1) = "Hello"
arr(6, 1) = "World"
arr(7, 1) = "Ciao"
arr(8, 1) = "mondo"
arr(9, 1) = "Salut"
arr(10, 1) = "terre"
arr(11, 1) = "Final"
Worksheets("Sheet1").Range("C3:C13").Value = arr
End Sub
If you now filter on cell C2 to show only all values greater than -75, you get a list:
-74.4398
-74.2028
-69.8689
-73.1567
-72.8093
-71.6604
If you click the button now for the VBA code to execute, you will see that the contents in cells C12 and C13 have the same meaning as in cell C3.
Why is this happening, why is he not filling the cells with the correct content?
I expected cell C12 to be "terre" and C13 "Final".
Is this an excel problem / error?
EDIT: This is worse as I thought, I used a different value for the filter -74, not only the last entries are wrong, all are wrong:
source to share
At first glance, I would say that this is not a bug, and this is more typical for arrays, however I could be completely wrong. If you were looking for a working solution, I went ahead and created this:
Sub Button2_Click()
Dim sht As Worksheet, lastrow As Integer, arr() As Variant
Set sht = ThisWorkbook.Worksheets("Sheet1")
lastrow = sht.Cells(sht.Rows.Count, "B").End(xlUp).Row
arr = Array("Hallo", "Welt", "Holla", "verdugón", "Hello", "World", "Ciao", "mondo", "Salut", "terre", "Final")
For i = 3 To lastrow
Range("C" & i).Value = arr(i - 3)
Next i
End Sub
source to share