Transpose Selected Cells Containing Specific Value

[dj44]

folks, good day

I know how to transpose a Column.

But what if I only want to transpose selected cells only.

for example all the cells containing "Apple"

I can select the cells - but i dont know how to transpose them into 1 string -to output to cell E1




Sub TransposeTest()

Dim oWs As Worksheet

Set oWs = ThisWorkbook.Worksheets("Sheet1")


With oWs
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A1:A" & LastRow).Select
End With


' What about All the cells containing Apple

oWs.Range("E1") = Join(Application.transpose(Selection)) ' Output RESULT to Cell E1


End Sub




Am i able to do this without filtering the data or how is the best way to do this

[mana]

oWs.Range("E1") = Join(Filter(Application.Transpose(Selection), "Apple", True))

[snb]

Somewhat less code:


Sub M_snb()
With ThisWorkbook.Sheets("Sheet1")
.cells(1,5) = Join(filter(Application.transpose(.cells(1).currentregion.columns(1)),"Apple"))
End with
End Sub

[dj44]

Thank you Mana & snb,

that's exactly what I've been looking for everywhere.

I am so happy i dont have to copy and paste and transpose, and filter.

I just wanted to transpose the cells that had a specific word in them and this does it perfect!

Thanks so much for the help

have a great day !