Hi Friends,
How to remove duplicates in a column of a excel sheet.
Kindly help me with the code.
thank you
Hi Friends,
How to remove duplicates in a column of a excel sheet.
Kindly help me with the code.
thank you
![]()
Please Login or Register to view this content.
Hope this helps
Sometimes its best to start at the beginning and learn VBA & Excel.
Please dont ask me to do your work for you, I learnt from Reading books, Recording, F1 and Google and like having all of this knowledge in my head for the next time i wish to do it, or wish to tweak it.
Available for remote consultancy work PM me
hi,
thanks for your reply. i would like to remove the duplicates from sheet 2, column 1, from first cellto cell n.
Well you'll have to work out the range and change A1:A17. That wasnt your post, this is a good start.
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
You need to be clearer on your post, and i'd learn about navigating the worksheet rather than plunging into things, it will help.![]()
Please Login or Register to view this content.
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.........
thats cause you need to put the remove duplicates code in.
:o)
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?
hi
Nathan already told you what you need to do....
see post#7
and #2![]()
Please Login or Register to view this content.
look carefully, combine them![]()
Regards, John55
If you have issues with Code I've provided, I appreciate your feedback.
In the event Code provided resolves your issue, please mark your Thread as SOLVED.
If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.
...enjoy -funny parrots-
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks