Remove duplicates in Excel

Remove duplicates:

Remove duplicates was a tough job in excel when we used office 2003 or older versions, where user left with no choice than writing some scripts to accomplish the job. Moreover the script used to takes time (based on data and formulas capacity in sheet) cause excel crash, unresponsive mode or long execution time. To overcome all complexities Excel release Remove Duplicates feature which removes duplicate data in single click within a second time. Let’s see how we can remove duplicates in excel. I would be taking following data (please refer figure 1.0) to perform remove duplicates operation.

Follow the steps:

1. Select the range of cells (“A1:B19“) that has duplicate values you want to remove. Tip: Remove any outlines or subtotals from your data before trying to remove duplicates.
2. Click Data tab > Remove Duplicates, and then Under Columns, check or uncheck (since our data source has headers hence we will check it please refer figure 1.1) the columns where you want to remove the duplicates. …
3. Click OK.

The output should look like as below:

Code examples to automate remove duplicates using VSTO. Since we are using VSTO code which refers to Addin technology in .Net hence following prerequisites are recommended to make code working:

• Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
• Create Excel Addin in C# or VB code style (Visual Studio Tools for Office)
• Excel 2010 or above
• Create a ribbon designer and put button

C# code example:

private void btnRemoveDuplicates_Click(object sender, RibbonControlEventArgs e)
{
	//Retain selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;

	//Remove duplicates
	oRange.RemoveDuplicates(1,excel.XlYesNoGuess.xlYes);
}

VB.Net code example:

Private Sub btnRemoveDuplicates_Click(sender As Object, e As RibbonControlEventArgs) Handles btnRemoveDuplicates.Click
	'Retain selection
	Dim oRange As excel.Range
	oRange=Globals.ThisAddIn.Application.Selection

	'Remove duplicates
	oRange.RemoveDuplicates(1,excel.XlYesNoGuess.xlYes)
End Sub

VBA code example:

Sub RemoveDuplicates()
    Dim a(2) As Integer
    a(0) = 1
    
    'Remove duplicates
    ActiveSheet.Range("A1:B19").RemoveDuplicates Columns:=Array(a(0)), Header:=xlYes
End Sub

Next >> Merge or consolidate data from different files in Excel example

Share on Facebook

Tweet