VBA code for removing duplicates

[jee007]

Hi,

I am trying to create a VBA code that would copy a column from another sheet and then remove any duplicate. The number of rows might vary.

Device: Microsoft Surface Pro (5th Gen) (Intel Core i5, GB RAM, 128GB), Windows 10 Pro
Excel product name and version number: Microsoft Office 365 version 2105

This is what I have. I know it is really wrong after the line that do the copying. I would really appreciate any help since this is my first time with VBA

Dim x, fila As Integer
Dim NumRows As Long
Dim Rng As Range
Worksheets("Mensual").Activate
Range("A2").Select
NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count
MsgBox NumRows
For x = 2 To 5
'For x = 2 To NumRows
fila = x
Worksheets("Empresas").Cells(fila, 1).Value = Worksheets("Mensual").Cells(fila, 1).Value
Worksheets("Empresas").Cells(fila, 1).Value = Range("A2", Range("A2").End(xlDown)).Rows.RemoveDuplicates Columns:=1, Header:=xlYes

Next
End Sub

[#2 · Accepted solution]

@jee007 Why bother learning VBA if you can do it with a few clicks and/or with standard Excel options.

Format the data range in "the other sheet" as a structured Excel table (Ctrl-T). Select a cell anywhere inside the table and you'll notice a Design ribbon at the top. Open it and look for the icon "Remove duplicates" and follow the instructions. 

Alternatively, you could look into the UNIQUE function. It displays only the unique values from a range in what is called a dynamic array. Thus, effectively removing duplicates.

[#3]

Thanks for the option. They just want to automatize the process, but I will look into that formula