How to remove duplicates in a column in excel

Hi Friends,

How to remove duplicates in a column of a excel sheet.

Kindly help me with the code.

thank you

hi,

thanks for your reply. i would like to remove the duplicates from sheet 2, column 1, from first cellto cell n.

Hi ,

im using the following code to remove duplicates.

Sub RemDupes3()
Application.ScreenUpdating = False
Dim ColN As Long
Dim MyS As Worksheet: Set MyS = ActiveSheet
Dim MyR As Range: Set MyR = MyS.Cells(1, 1).CurrentRegion
Dim NumCol As Long: NumCol = MyR.Columns.Count
Dim MyArray As Variant: ReDim MyArray(0 To NumCol - 1)
For ColN = 1 To NumCol
MyArray(ColN - 1) = ColN
Next
MyR.RemoveDuplicates Columns:=(MyArray), Header:=xlYes
Dim rowcount As Long, i As Long, j As Long, k As Boolean
rowcount = MyR.Rows.Count
For i = rowcount To 1 Step -1
k = 0
For j = 1 To NumCol
If MyR.Value2(i, j) <> "" Then
k = 1
Exit For
End If
Next j
If k = 0 Then
MyR.Rows(i).Delete Shift:=xlUp
End If
Next i
Application.ScreenUpdating = True
End Sub

i would like to remove the duplicates from sheet 2, column 1, from first cellto cell n. please help

Dim MyS As Worksheet: Set MyS = ActiveSheet

i think i need to change this alone. please help

Hi nathan,

thank you,

but it shows error. Kindly help as im a fresher in VB scripting

my exact query is to remove the duplicates from sheet2, column 1, cells 1 to n

lastRow= worksheets("Sheet2").range("a100000").end(xlup).row
set rngRemovingDupes=worksheets("Sheet2").range("a1:a" & lastrow)
rngRemovingDupes.removedup.........

returns error in the last line

rngRemovingDupes.removedup.........

hi nathan,

i do understand.. but dont know how to do..

kindly reframe my code as im fresher here..

thanks a lot

Sub RemDupes3()
Application.ScreenUpdating = False
Dim ColN As Long
Dim MyS As Worksheet: Set MyS = ActiveSheet
Dim MyR As Range: Set MyR = MyS.Cells(1, 1).CurrentRegion
Dim NumCol As Long: NumCol = MyR.Columns.Count
Dim MyArray As Variant: ReDim MyArray(0 To NumCol - 1)
For ColN = 1 To NumCol
MyArray(ColN - 1) = ColN
Next
MyR.RemoveDuplicates Columns:=(MyArray), Header:=xlYes
Dim rowcount As Long, i As Long, j As Long, k As Boolean
rowcount = MyR.Rows.Count
For i = rowcount To 1 Step -1
k = 0
For j = 1 To NumCol
If MyR.Value2(i, j) <> "" Then
k = 1
Exit For
End If
Next j
If k = 0 Then
MyR.Rows(i).Delete Shift:=xlUp
End If
Next i
Application.ScreenUpdating = True
End Sub

the above one is the code im using now.. i think i need to change the following line alone as i need to remove the duplicates in sheet 2 only

Dim MyS As Worksheet: Set MyS = ActiveSheet

here what is meant by active sheet. sheet1 or sheet 2?