• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 318
  • Last Modified:

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
  • 3
1 Solution
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)?

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,

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

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now