I recently had to rewrite my JoinRange function because Application.Transpose() stopped working on me.

Public Function JoinRange(JoinAddress As Range, Optional Delimiter As String = ",") As String
    'JoinRange = Join(Application.Transpose(Application.Transpose(JoinAddress.Value)), Delimiter)
    
    '4/10/2024 Excel Version: 14.0.4760.1000 (32-bit)
    Dim s As String
    Set sb = CreateObject("System.Text.StringBuilder")
    For Each c In JoinAddress.Cells
        s = c.Value2
        sb.Append_3 s
        sb.Append_3 Delimiter
    Next c
    s = sb.ToString()
    JoinRange = Left(s, Len(s) - Len(Delimiter))
End Function
You can get the same results with a mess of concat cell-formulas, but I've used this function often enough that I find it cleaner to read and quicker to type.

General uses:
+ csv the copy/paste way: add a formula column AA to define the row data =JoinRange(A1:Z1), and a at the bottom of AA add =JoinRange(AA1:AA100,Char(10)&Char(13))
+ multi-indexed vLookups: add a formula column in the data to define the key D1=JoinRange(DataSheet!A1:C1,":"), then lookup by the key =VLOOKUP(JoinRange(TargetSheet!A1:C1,":"),DataSheet!D1:Z100,15,FALSE)

also one nice added benefit of the rewrite is more versatility
=JoinRange((A1,C1,B1))