+ Reply to Thread
Results 1 to 16 of 16

How to remove duplicates in a column in excel

  1. #1
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Talking 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

  2. #2
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to remove duplicates in a column in excel

    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

  3. #3
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: How to remove duplicates in a column in excel

    hi,

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

  4. #4
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to remove duplicates in a column in excel

    Well you'll have to work out the range and change A1:A17. That wasnt your post, this is a good start.

  5. #5
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: How to remove duplicates in a column in excel

    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

  6. #6
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: How to remove duplicates in a column in excel

    Dim MyS As Worksheet: Set MyS = ActiveSheet

    i think i need to change this alone. please help

  7. #7
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to remove duplicates in a column in excel

    Please Login or Register  to view this content.
    You need to be clearer on your post, and i'd learn about navigating the worksheet rather than plunging into things, it will help.

  8. #8
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: How to remove duplicates in a column in excel

    Hi nathan,

    thank you,

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

  9. #9
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: How to remove duplicates in a column in excel

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

  10. #10
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: How to remove duplicates in a column in excel

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

    returns error in the last line

  11. #11
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: How to remove duplicates in a column in excel

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

  12. #12
    Valued Forum Contributor
    Join Date
    09-21-2011
    Location
    Birmingham UK
    MS-Off Ver
    Excel 2003/7/10
    Posts
    2,188

    Re: How to remove duplicates in a column in excel

    thats cause you need to put the remove duplicates code in.

    :o)

  13. #13
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Talking Re: How to remove duplicates in a column in excel

    hi nathan,

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

    kindly reframe my code as im fresher here..

    thanks a lot

  14. #14
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: How to remove duplicates in a column in excel

    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

  15. #15
    Registered User
    Join Date
    12-05-2013
    Location
    india
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: How to remove duplicates in a column in excel

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

  16. #16
    Valued Forum Contributor john55's Avatar
    Join Date
    10-23-2010
    Location
    Europe
    MS-Off Ver
    Excel for Microsoft 365
    Posts
    2,062

    Re: How to remove duplicates in a column in excel

    hi
    Nathan already told you what you need to do....
    see post#7
    Please Login or Register  to view this content.
    and #2
    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-

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 05-08-2014, 07:24 AM
  2. remove duplicates in column
    By MsLilly in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-29-2009, 01:08 PM
  3. Remove duplicates in column D
    By maxebi in forum Excel General
    Replies: 3
    Last Post: 03-02-2009, 02:36 AM
  4. Possible To Remove Duplicates In A Column
    By fredmeister in forum Excel General
    Replies: 3
    Last Post: 04-02-2008, 05:19 PM
  5. How to remove duplicates from a column
    By wali in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 01-14-2008, 11:33 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1