vb macro to delete duplicates from current colum

Posted on 2011-10-24
Last Modified: 2012-05-12
here i am offering 2000 points for a vb excel 2010 macro which would
-delete duplicate entries in the whole column

then i need to move to another column and run that macro on that column etc.

or an automated macro which would jump to the next column automatically
the number of columns and rows counts in hundreds
Question by:theruck
    LVL 27

    Expert Comment

    by:Glenn Ray
    1) What version of Excel are you using?  (Can use "Remove Duplicates" function in Excel 2007)
    2) Is your data sorted?
    3) Do you wish to only delete duplicates within a column of data, as opposed to entire rows (I assume the former)?

    LVL 41

    Accepted Solution

    Note, the Remove Duplicates Function exists in the Excel Ribbon under the Data->Data Tools tab.  However, if you need this automated, the following is your solution...

    Here's a simple macro you can use to remove duplicates with Excel 2007+.  
    You are prompted for a starting/ending column letter, and the macro removes the duplicates from that column, on a column by column basis.  Headers to the data are assumed (thus Header:=xlYes).  There is NO error checking, so you have to type in a vaild first letter and last column letter.

    Here's the code:

    Sub removeDups()
    Dim wkb As Workbook
    Dim wks As Worksheet
    Dim getInput1 As String
    Dim getInput2 As String
    Dim colNo1 As Long
    Dim colNo2 As Long
    Dim col As Long
        Set wkb = ActiveWorkbook
        Set wks = ActiveSheet
        getInput1 = InputBox("Enter Starting Column Letter to Remove Duplicates", "A")
        getInput2 = InputBox("Enter Ending Column Letter to Remove Duplicates", "IV")
        colNo1 = wks.Range(getInput1 & "1").Column
        colNo2 = wks.Range(getInput2 & "1").Column
        For col = colNo1 To colNo2
          wks.Cells(1, col).EntireColumn.RemoveDuplicates Columns:=1, Header:=xlYes
        Next col
    End Sub

    Open in new window

    See attached,

    LVL 14

    Author Comment

    perfect. that is what i was looking for
    LVL 14

    Author Comment

    thank you
    LVL 14

    Author Closing Comment

    thank you. works like a charm

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
    Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
    This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now