Count number of different cells in VBA

Question

Hello,

I want to count no of different cells which are selected using VBA.

Consider if we select five distinct cells - D5, C2, E7, A4, B1. Is there a way I can count these number of cells.

Secondly how can I retrieve data in these cells. Lets say I want to store it in an array.

Thank you for the help.

Answer 1

Thankfully I got a way around it by doing - Selection.Cells.Count

It returns me the cell count for selected cells.

But I am still stuck with dynamically assigning this value to an array as in ---

I = Selection.Cells.Count Dim ValArr(I)

Answer 2

Dim rngCell as Range, arrArray() as Variant, i as integer

Redim arrArray(1 to Selection.Cells.Count)

i = 1
For each rngCell in Selection

    arrArray(i) = rngCell.Value
    i = i + 1

Next

Answer 3

Looks like you got it mostly figured out, but here is something to load it into an array if you want it:

Public Sub Example()
    Dim test() As Variant
    test = RangeToArray(Excel.Selection, True)
    MsgBox Join(test, vbNewLine)
End Sub

Public Function RangeToArray(ByVal rng As Excel.Range, Optional ByVal skipBlank As Boolean = False) As Variant()
    Dim rtnVal() As Variant
    Dim i As Long, cll As Excel.Range
    ReDim rtnVal(rng.Cells.Count - 1)
    If skipBlank Then
        For Each cll In rng.Cells
            If LenB(cll.Value) Then
                rtnVal(i) = cll.Value
                i = i + 1
            End If
        Next
        ReDim Preserve rtnVal(i - 1)
    Else
        For Each cll In rng.Cells
            rtnVal(i) = cll.Value
            i = i + 1
        Next
    End If
    RangeToArray = rtnVal
End Function