T4324_Join Columns Using Evaluate Sort Ascending

https://excel-egy.com/forum/t4324
---------------------------------

Sub Join_Columns_Using_Evaluate_Sort_Ascending()
    Dim e, x, r As Range, delim As String, s As String

    Application.ScreenUpdating = False
        delim = "|"
        For Each e In Array("B1", "I1")
            With Range(e).CurrentRegion.Offset(1)
                Set r = .Columns(.Columns.Count).Resize(.Rows.Count - 1)
                s = s & IIf(s <> "", delim, "") & Join(Filter(r.Parent.Evaluate("TRANSPOSE(IF(" & r.Address & "<>""""," & r.Address & "))"), False, 0), delim)
            End With
        Next e
        x = Split(s, delim)

        With Range("M2")
            .Resize(UBound(x) + 1).Value = Application.Transpose(x)
            .CurrentRegion.Sort Range("M2"), xlAscending, , , , , , xlNo
        End With
    Application.ScreenUpdating = False
End Sub