Solved: Generate Comma Separated string from columns based on condition in Excel using VBA

This seems to be working for me. It relies on the AUtoFilter to do its voodoo:

Sub MakeTheString()
    
    Dim LastR As Long
    Dim LastC As Long
    Dim Result As String
    
    Result = "<No data found!>"
    
    With ActiveSheet
        LastR = .Cells(.Rows.Count, 1).End(xlUp).Row
        LastC = .Cells(1, .Columns.Count).End(xlToLeft).Column
        .Cells(1, LastC + 1) = "Original"
        With .Range(.Cells(2, LastC + 1), .Cells(LastR, LastC + 1))
            .Formula = "=ROW(A1)"
            .Value = .Value
        End With
        .[a1].Sort Key1:=.[c1], Key2:=.Cells(1, LastC + 1), Order1:=xlAscending, Order2:=xlAscending, _
            Header:=xlYes
        .[a1].AutoFilter
        .[a1].AutoFilter 3, "Yes", xlAnd
        On Error Resume Next
        Result = Join(Application.Transpose(.Range(.Cells(2, 1), .Cells(LastR, 1)).SpecialCells(xlCellTypeVisible)), ", ")
        On Error GoTo 0
        .[a1].AutoFilter
        .[a1].Sort Key1:=.Cells(1, LastC + 1), Order1:=xlAscending, Header:=xlYes
        .Cells(1, LastC + 1).EntireColumn.Delete
    End With
    
    MsgBox Result
    
End Sub

Open in new window