Type Mismatch at Redim

0
votes

The code below joins a range of cells into an string, credit for the this goes to JNevill from my previous post.

I keep getting a type mismatch error at the redim area. I also tried wrapping it around a Ubound before subtracting the 1, but that still gave me a type mismatch error. 

Function convertRangetoList(myRange As Range, delimiter As String) As String
Dim rngCell As Range
Dim arrNames() As Variant
Dim i As Long

'redim the array
ReDim arrNames(myRange - 1)


'Load the array
i = 0
For Each rngCell In myRange
    arrNames(i) = rngCell.Value
    i = i + 1
Next

    ' Join to comma seperate array
    convertRangetoList = Join(arrNames, delimiter)

End Function
1
vbaexcel

1 Answers

1
votes

Redim is expecting a Number not a Range object. Use Range.CountLarge to return the total count of all the cells in all the Areas of the Range.

ReDim arrNames(myRange.CountLarge - 1)