Search code examples
excelvbarange

Add visible cells of a range to array

I am trying to get the values of the visible cells of a range into an array.

My code makes the array carry the values until the first non visible cell then stops.

Public Function ListeMaschinen() As Variant

Dim Auswahl As Range

With Sheets("qry_TechnischesDatenblatt")
Set Auswahl = .Range(.Range("A2:B2"), .Range("A2:B2").End(xlDown)).SpecialCells(xlCellTypeVisible)
End With

ListeMaschinen = Auswahl

End Function

If I select the range it shows all the cells I want marked.

Auswahl.Select
Solution

  • Here I have added the range cells to an array. 

    Sub examp()
Dim rng As Range, cll As Range, i As Integer, a(100) As Variant
Set rng = Range(Range("A2:B2"), Range("A2:B2").End(xlDown)).SpecialCells(xlCellTypeVisible)
i = 0
For Each cll In rng
a(i) = cll.Value
i = i + 1
Next
End Sub