• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenate..

Hello Deepak.. If one of the cell is empty it shows like 100, 101, , 103, 104, 105, 106, , 108, 109 ...

What I want it to show as if some cells are empty to ignore that cells. In this example to show as 100, 101, 103, 104, 105, 106, 108, 109 ...
 
check this

Code: 
Option Explicit

Public Function myJoin(a As Variant) As String
Dim sep As String: sep = "-"
 
    Dim y As Variant
    If TypeOf a Is Range Then
        For Each y In a.Cells
            If Len(y) > 0 Then myJoin = myJoin & y.Value & sep
        Next y
    ElseIf IsArray(a) Then
        For Each y In a
            If Len(y) > 0 Then myJoin = myJoin & y & sep
        Next y
    Else
        If Len(y) > 0 Then myJoin = myJoin & a & sep
    End If
    myJoin = Left(myJoin, Len(myJoin) - Len(sep))
End Function
 
You must log in or register to reply here.
Back
Top