Vba

[Macro212]

Hello, I would like to use a VBA code to remove the duplicates in Sheet 1 column A and then copy and paste the list (without duplicates) to Sheet 2 column A.

Example:

Sheet 1

Column A:
John
Jim
Alice
John
Jim
Tim
Tim
Tom

So I want the VBA code to remove the duplicates and paste the new list without duplicates. I have provided the example below.

Sheet 2

Column A:
John
Jim
Alice
Tim
Tom

 

[Joe4]

Why not just use Excel's built in "Remove Duplicates" functionality?
It can be used in VBA too.
To get the code, you can just turn on the Macro Recorder, and record yourself doing it manually.
If you need helping cleaning up the code afterwards (or making it more dynamic), paste your code here along with letting us know what you need to add to it.

 

[Macro212]

The data in the column is active. It is linked to another sheet. So the "Remove Duplicates" functionality doesnt work when the data in the column is linked other cells. And I want the cleaned up list in a different sheet.

 

[MARK858]

Please explain exactly what you mean by "linked".

 

[Fluff]

How about

Sub Macro212()
   Dim Cl As Range
   Dim Ws As Worksheet
   
   Set Ws = Sheets("Sheet1")
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For Each Cl In Ws.Range("A1", Ws.Range("A" & Rows.Count).End(xlUp))
         If Cl.Value <> "" Then .Item(Cl.Value) = Empty
      Next Cl
      Sheets("sheet2").Range("A1").Resize(.Count).Value = Application.Transpose(.Keys)
   End With
End Sub

 

[Macro212]

That worked wonderfully!! Thank you so much.

 

[Fluff]

You're welcome & thanks for the feedback