PDA

View Full Version : [SOLVED] Transpose Column - But Exclude Rows - Found in Array List



dj44
06-12-2018, 03:52 AM
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
06-12-2018, 04:32 AM
??


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
06-12-2018, 06:38 AM
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
06-12-2018, 06:58 AM
oWs.Range("B1").Value = Join(oArray)

dj44
06-12-2018, 07:42 AM
Thanks Mana,

for your generous help!

Thats pretty neat

I can transpose my column values without making a big mess :grinhalo:

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

Kudos :beerchug:

Have a great week!