Problems to store visible cells into array

Hello to all,

Please some help on this.

I'm trying to store into an array the values of filtered/visible cells in range A2:A10. With the code below
the array only is loaded with the first 3 values (B,C,D), where should be B, C, D, T, K.

The filter is applied based on those cells colored in red.

I'm attaching a sample file for your reference.

Sub Test()

Range("A2:A10").AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor

Arr = Range("A2:A10").SpecialCells(xlCellTypeVisible).Value

End Sub

Thanks in advance.

Filtered (Visible) cells can only take contagious cells, which means the array can only take two rows at most, but you can set it to a range.

Hi AB33,

Thanks for answer.

I've tried setting to range but when I see the content of "rng" in "Locals" windows it's still only taking the first 3 filtered values.

I've done as below.

Sub Test()
Dim rng As Range

Range("A2:A10").AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor

Set rng = Range("A2:A10").SpecialCells(xlCellTypeVisible)

End Sub

Originally Posted by cgkmal

I've tried setting to range but when I see the content of "rng" in "Locals" windows it's still only taking the first 3 filtered values.

I've done as below.

Sub Test()
Dim rng As Range

Range("A2:A10").AutoFilter Field:=1, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor

Set rng = Range("A2:A10").SpecialCells(xlCellTypeVisible)

End Sub

Array can store continuous block of cells.
That means it is equivalent to

Arr = Range("A2:A10").SpecialCells(xlCellTypeVisible).Areas(1).Value

So, if you want all the filtered value in an array, you need to copy the filtered data in somewhere then store in an array.

Thank you. Understood.

One more question.

How to set an "if condition" to chek if in the range A1:A10 there is no cells colored in red?

Thanks again.

I have ameended your code. I have also added my own code.

deleted duplicate

Thanks jindon for the example. Exactly to understaand how to set that condition and some other very good techniques you always use.

Regards

One way

Sub test()
    Dim myRange As Range, r As Range, x As Range, ff As String
    With Application.FindFormat
        .Clear
        .Interior.Color = vbRed
    End With
    Set myRange = Range("a1:a10")
    Set r = myRange.Find("", searchformat:=True)
    If Not r Is Nothing Then
        ff = r.Address
        Do
            If x Is Nothing Then
                Set x = r
            Else
                Set x = Union(x, r)
            End If
            Set r = myRange.Find("*", r, searchformat:=True)
        Loop Until r.Address = ff
    End If
    MsgBox IIf(r Is Nothing, "No cells", x.Address)
End Sub

No problem.