Share via

Removing Duplicate Values in a Range in Excel 2010

  • Article

Office Quick Note banner

Handy Programming Tips for Microsoft Excel 2010: Learn how to remove duplicate values in a range of Microsoft Excel 2010 data.

Applies to: Excel 2010 | Office 2010 | VBA

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

Published:   May 2011

Provided by:    Frank Rice, Microsoft Corporation

To reduce clutter, you might want to remove duplicate values in a range of data. In this topic, you programmatically add data to a worksheet and then remove any duplicate values from that data. To complete this task, you must do the following:

  • Add the Code to the Visual Basic Editor

  • Test the Solution

Add the Code to the Visual Basic Editor

In this task, you add programming code that creates an array of data and then removes the duplicate values in that range.

To add code to the Visual Basic Editor

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note

    If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.

  3. In the Projects pane, click Sheet1.

  4. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub TestRemoveDuplicates()
  ' Set up the data:
  Range("A1:C1") = Array("ID", "Name", "Price")
  Range("A2:C2") = Array(1, "North", 12)
  Range("A3:C3") = Array(2, "East", 13)
  Range("A4:C4") = Array(3, "South", 24)
  Range("A5:C5") = Array(4, "North", 12)
  Range("A6:C6") = Array(5, "East", 23)
  Range("A7:C7") = Array(6, "South", 24)
  Range("A8:C8") = Array(7, "West", 10)
  Range("A9:C9") = Array(8, "East", 23)

  ' Make sure that you look at the current state of the
  ' data before you remove duplicate rows.

  ' Remove duplicates, looking for unique values in columns 2 and 3.
  UsedRange.RemoveDuplicates Columns:=Array(2, 3), Header:=xlYes

End Sub

Test the Solution

In this task, you step through the VBA code that adds data to Sheet 1 and then uses the RemoveDuplicates method of the Range object to remove duplicate values.

To run the code

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.

  3. Press F8 to step through the code line-by-line and watch the code interact with Excel.

Next Steps