Macro to take ten columns, make 1 column and remove dups and put in a row

[tonywatsonhelp]

Hi Everyone,

Please help as i'm having a bit a trouble with this.

I need a macro to do the following for me,

get the data from Columns H,I,J,K,L,M,N,O,P,Q (Starting at Row 53 and down to last row, best to use column D for last row as this always has data in it for every row in use)
make into one column either by putting in sheet "Control" column BB or just in its memory, remove duplicates, the pleace the results in "BG50"

please help if you can

Thanks

Tony

 

[Fluff]

Do you want BG50 to have the complete list? or do you want the list to be in BG50,BH50 etc?

 

[Fluff]

Based on the title, try

Sub tonywatsonhelp()
   Dim Ary As Variant
   Dim r As Long, c As Long
   
   Ary = Range("H53", Range("D" & Rows.Count).End(xlUp).Offset(, 13)).Value2
   With CreateObject("scripting.dictionary")
      For c = 1 To UBound(Ary, 2)
         For r = 1 To UBound(Ary)
            .Item(Ary(r, c)) = Empty
         Next r
      Next c
      Range("BG50").Resize(, .Count).Value2 = .Keys
   End With
End Sub

 

[tonywatsonhelp]

Thanks Fluff,
this is great,
and exactly what I wanted.
Tony

 

[Fluff]

You're welcome & thanks for the feedback