Transpose Column - But Exclude Rows - Found in Array List

[dj44]

Hi folks,

Can some one please be able to advise on how I may use an exclude array to exclude items in my transpose array

 Sub Transpose_Array()
    
  
  

    Dim oWs As Worksheet

    Set oWs = ThisWorkbook.Worksheets("Test")
    
    
    
    oExclude = oArray = Array("Hello", "Car","Mobile")
    For i = LBound(oExclude) To UBound(oExclude)
    
      With oWs
      LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
     .Range("A1:A" & LastRow).Select
    End With
    
    
    oWs.Range("B1") = Join(Filter(Application.Transpose(Selection), oExclude(i), True))   
    
     Next i
    End Sub

I usually exlude one term at a time but I think an array can hold my exclude terms?

[mana]

??

Option Explicit


Sub Transpose_Array()
    Dim oWs As Worksheet
    Dim oExclude, oArray
    Dim i As Long


    oExclude = Array("Hello", "Car", "Mobile")
    
    Set oWs = ThisWorkbook.Worksheets("Test")
     oArray = Application.Transpose(oWs.Range("A1", oWs.Cells(Rows.Count, "A").End(xlUp)))


    For i = LBound(oExclude) To UBound(oExclude)
        oArray = Filter(oArray, oExclude(i), False)
     Next i
     
     oWs.Range("B1").Resize(, UBound(oArray) + 1).Value = oArray
     
End Sub

[dj44]

Hello Mana,

thank you for your awesome help!

This works beautifully.

I had one question if its not too much trouble

I have not been able to work out how to put the complete result in only 1 cell B1
instead of each term in a separate cell



oWs.Range("B1") = Transposed Result


'below is wrong -
oWs.Range("B1") = (UBound(oArray) + 1).Value = oArray


any pointers would be grateful

[mana]

oWs.Range("B1").Value = Join(oArray)

[dj44]

Thanks Mana,

for your generous help!

Thats pretty neat

I can transpose my column values without making a big mess

All the values i want i can select - exclude the ones i dont want and make a nice transposition of it

Kudos

Have a great week!