Adding a count to concatenate range function

I am desperate!

You need to wrap the code with the code tag.
i.e
[code]
VBA code here
[/code]

It is a MUST rule here.

Apologies, I am very green to these forums. Thanks.

Function ConcatenateRange(ByVal cell_range As Range, _
                    Optional ByVal seperator As String) As String

Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long

cellArray = cell_range.Value

For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) <> 0 Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next

If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If

ConcatenateRange = newString

End Function

for example

Function ConcatenateRange(ByVal cell_range As Range, _
                          Optional ByVal seperator As String) As String
Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long, k As Long

cellArray = cell_range.Value
For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) Then k = k + 1: newString = newString & _
                                        seperator & k & ")" & cellArray(i, j)
    Next
Next
If Len(newString) Then newString = Right$(newString, (Len(newString) - Len(seperator)))
ConcatenateRange = newString
End Function

Thanks for the code tag and I hope you can edit your first post as well.

Here's an idea

Function ConcatenateRange(ByVal cell_range As Range, _
                Optional ByVal separator As String = " ") As String
    Dim e, n As Long
    For Each e In cell_range.Value
        If e <> "" Then
            n = n + 1
            ConcatenateRange = ConcatenateRange & separator & n & ") " & e
        End If
    Next
    If Len(ConcatenateRange) Then ConcatenateRange = Mid$(ConcatenateRange, Len(separator) + 1)
End Function

Jindon, you are a life saver. It works perfectly thank you! +rep!

You are welcome and thanks for the edit.