Take a column list and break into multiple comma delimited rows

user_7577334 0
Viewed 61

The code below takes the list in Column A and turns the entire column into a comma delimited list in cell B1. Instead, I want to be able to control the range in Column A and control which cell the results go to. For example, I want to put A1:A4 in B1 and then A5:A8 in B2. Anyone know how to code this. Thanks

Sub test()
    Range("B1") = Join(Application.Transpose(Range("A1", Range("A" & Rows.Count).End(xlUp)).Value), ",")
End Sub

I want the results to look like the second group in the picture below.

enter image description here

excel-formula vba excel
1 Answers

If you're having trouble with TextJoin and it's definitely not working (I'd be pushing to get it working rather than working around it) then this will work for you, it's the same sort of concept.

Public Function MyTextJoin(ByVal strDelimiter As String, ByVal bIgnoreGaps As Boolean, ByVal rngValues As Range) As String
    Dim objCell As Range, strValue As String, bAddValue As Boolean
    
    For Each objCell In rngValues
        bAddValue = False
        strValue = objCell.Text
            
        If strValue <> "" Then
            bAddValue = True
        Else
            If Not bIgnoreGaps Then bAddValue = True
        End If
        
        If bAddValue Then MyTextJoin = MyTextJoin & strDelimiter & strValue
    Next
    
    If Left(MyTextJoin, 1) = "," Then MyTextJoin = Mid(MyTextJoin, 2)
End Function

FYI - TextJoin is only available in later versions of Excel so if the formula doesn't exist at all, that'll be why.

https://support.microsoft.com/en-us/office/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c

user_57720950
Related