Passing contiguous / non-contiguous selected cells to an array

[RawlinsCross]

Good day,

Have a worksheet where I'd like to select cells in column A (they could be contiguous or non-contiguous) and place the values in an array.

Seems simple enough (vArray = Selection.value works for contiguous selection a group of cells) but I'm having trouble with the non-contiguous part. If I select say A1, A3, A5 using the ctrl-key the resulting array (using vArray = Selection.value) contains only A1. Any thoughts on something that can handle both contiguous AND non-contiguous cases?

 

[Joe4]

If you have selected various cells in column (contiguous or non-contiguous), the following should put them all in an array:

Sub BuildArray()

    Dim arr() As Variant
    Dim cell As Range
    Dim n As Long
    
    ReDim arr(1 To 10000)
    n = 0
    
    For Each cell In Selection
        n = n + 1
        arr(n) = cell.Value
    Next cell
    
    ReDim Preserve arr(1 To n)
    
End Sub

 

[RawlinsCross]

Very straight forward - thanks!

 

[Joe4]

You are welcome.