trying this code am i missing something I can't get it to work?

%3CLINGO-SUB%20id%3D%22lingo-sub-2620726%22%20slang%3D%22en-US%22%3Etrying%20this%20code%20am%20i%20missing%20something%20I%20can't%20get%20it%20to%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2620726%22%20slang%3D%22en-US%22%3E%3CP%3EFunction%20transposeRange(Rg%20As%20Range)%3CBR%20%2F%3E'Created%20to%20put%20all%20dynamic%20values%20in%20one%20column%20inside%20one%20row%20in%20one%20cell%3CBR%20%2F%3EDim%20xCell%20As%20Range%3CBR%20%2F%3EDim%20xStr%20As%20Range%3CBR%20%2F%3EFor%20Each%20xCell%20In%20Rg%3CBR%20%2F%3EIf%20Not%20IsEmpty(xCell.Value)%20Then%3CBR%20%2F%3ExStr%20%3D%20xStr%20%26amp%3B%20xCell.Value%20%26amp%3B%20%22%2C%22%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%3CBR%20%2F%3EtransposeRange%20%3D%20Left(xStr%2C%20Len(xStr)%20-%201)%3CBR%20%2F%3EEnd%20Function%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2620726%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2620919%22%20slang%3D%22en-US%22%3ERe%3A%20trying%20this%20code%20am%20i%20missing%20something%20I%20can't%20get%20it%20to%20work%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2620919%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1122367%22%20target%3D%22_blank%22%3E%40RoweeYadin%3C%2FA%3E%26nbsp%3BNot%20sure%20what%20you%20want%20to%20achieve%2C%20but%20by%20the%20name%20of%20the%20function%20you%20want%20to%20transpose%20a%20range%2C%20Are%20you%20aware%20that%20there%20is%20a%20function%20called%20TRANSPOSE%3F%20Depending%20on%20your%20Excel%20version%20you%20may%20have%20to%20enter%20it%20with%20Ctrl-Shift-Enter%2C%20rather%20than%20jus%20Enter.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

Function transposeRange(Rg As Range)
'Created to put all dynamic values in one column inside one row in one cell
Dim xCell As Range
Dim xStr As Range
For Each xCell In Rg
If Not IsEmpty(xCell.Value) Then
xStr = xStr & xCell.Value & ","
End If
Next
transposeRange = Left(xStr, Len(xStr) - 1)
End Function

2 Replies

@RoweeYadin Not sure what you want to achieve, but by the name of the function you want to transpose a range, Are you aware that there is a function called TRANSPOSE? Depending on your Excel version you may have to enter it with Ctrl-Shift-Enter, rather than jus Enter.

You need to declare xStr as string, not range. But, if you have office 2016 or higher, you should check if you have the built-in textjoin function, which I believe will do what you need.