Excel fill cell range after applying filter

Question

I want to apply a filter on a set of data, after that I would like to fill a range of data into a range of cells. But now if this will be done, the last two rows are getting 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

start

This is the list of numbers in cell B3 to B13. One button with the following VBA code behind:

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 set a filter on cell C2 to just show all values greater than -75 you will get a list like that:

-74.4398
-74.2028
-69.8689
-73.1567
-72.8093
-71.6604

after filter

If you now press the button so that VBA code will be executed, you will see that the content in cell C12 and C13 has the same value as in cell C3.

fill range

Why does it happened, why does it not fill up the cells with the right content ?

I expected that cell C12 will be 'terre' and C13 'Final'.

Is that an excel issue / bug ?

EDIT: It is more worst as I thought, I used now a different value for the filter -74 and not only the last entries are incorrect, all are incorrect:

new filter applied

My "guess" would be that when you apply the filter, the array range is broken up. So when you press your macro, it begins filling in the range, but when it gets to `arr(5, 1) = "Hello"`, the range doesn't match any longer, and it just attempts to start over. Same thing with the last line, the range doesn't match, so it attempts to start over again. That's a complete shot in the dark from me! — dwirony, Aug 01 '17 at 13:15
thanks for that, but is it then a bug from MS excel or not. Sounds like this — Doe Wayne, Aug 01 '17 at 13:18

score 1 · Answer 1 · answered Aug 01 '17 at 13:28

1

I believe it is really a bug and it seems it has been there for some time - see this LINK.

answered Aug 01 '17 at 13:28

Pavel_V

1,220
1
11
17

Thanks for that link, this describe nearly the same thing – Doe Wayne Aug 01 '17 at 13:43

score 0 · Answer 2 · answered Aug 01 '17 at 13:34

At first glance I would say it's not a bug, and it's moreso the nature of 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

thanks for that example, the answer from Pavel_V is good and I hope that MS will solve the issue in later releases, maybe we have to raise a ticket there again. — Doe Wayne, Aug 01 '17 at 13:46

Excel fill cell range after applying filter

2 Answers2