VBA for list from another coulmn each value once

[amit96]

hello

i need a VBA code that gives me a list in cell B20 that contains values from sheet1 coulmn B:B.
if value is writen 2 times, so give it to me only once. if value is writen oce, give it to me as well.
each valuse only once.

thank you!

 

[JEC]

Gives the output in column C

Sub jec()
 Dim ar As Variant, i As Long
 ar = Range("B1", Range("B" & Rows.Count).End(xlUp))
 
 With CreateObject("scripting.dictionary")
   For i = 1 To UBound(ar)
      .Item(ar(i, 1)) = Empty
   Next
   Cells(1, 3).Resize(.Count) = Application.Transpose(.keys)
 End With
End Sub

 

[amit96]

Gives the output in column C

Sub jec()
 Dim ar As Variant, i As Long
 ar = Range("B1", Range("B" & Rows.Count).End(xlUp))
 
 With CreateObject("scripting.dictionary")
   For i = 1 To UBound(ar)
      .Item(ar(i, 1)) = Empty
   Next
   Cells(1, 3).Resize(.Count) = Application.Transpose(.keys)
 End With
End Sub

I dont understand. how does it know to take the data from sheet1 coulmn B? and the output list should appear in sheet2 coulmn B20

 

[amit96]

Gives the output in column C

Sub jec()
 Dim ar As Variant, i As Long
 ar = Range("B1", Range("B" & Rows.Count).End(xlUp))
 
 With CreateObject("scripting.dictionary")
   For i = 1 To UBound(ar)
      .Item(ar(i, 1)) = Empty
   Next
   Cells(1, 3).Resize(.Count) = Application.Transpose(.keys)
 End With
End Sub

i only need to know how to take the data from sheet1 in coulmn B. all the rest i figuerd out