vb macro to delete duplicates from current colum

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
LVL 14
Who is Participating?
dlmilleConnect With a Mentor Commented:
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,

Glenn RayExcel VBA DeveloperCommented:
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)?

theruckAuthor Commented:
perfect. that is what i was looking for
theruckAuthor Commented:
thank you
theruckAuthor Commented:
thank you. works like a charm
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.