Removing Duplicate Range in a Bigger Range with Excel Formula

A duplicate range is a subset of cells in a bigger range that have the same values as another subset of cells in the same bigger range. For example, if you have a range of data from A1 to C6, and the values in A2 to A6 are the same as the values in B2 to B6, then A2 to A6 is a duplicate range of B2 to B6.

To remove duplicate range in a bigger range, you need to use the Range.RemoveDuplicates method in VBA. This method allows you to specify which columns or rows are checked for duplicate values, and whether the data has a header row or not. The method will delete any rows or columns that have duplicate values, and keep the first occurrence of the unique values.

Procedures

To use the Range.RemoveDuplicates method in VBA, you need to follow these steps:

• Define the bigger range that you want to remove duplicates from. You can use the Range object to refer to a specific range of cells, or the UsedRange property to refer to the entire range of data on the worksheet.
• Specify which columns or rows are checked for duplicate values. You can use an array of numbers to indicate the column or row index, or a single number to indicate a single column or row. For example, Columns:=Array(1, 2) means that the first and second columns are checked for duplicates, and Columns:=1 means that only the first column is checked for duplicates.
• Specify whether the data has a header row or not. You can use the constants xlYes, xlNo, or xlGuess to indicate if the first row of the data contains headers or not. For example, Header:=xlYes means that the first row is a header row, and Header:=xlNo means that the first row is not a header row.
• Call the Range.RemoveDuplicates method with the parameters you defined. For example, Range("A1:C6").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes means that the range from A1 to C6 is checked for duplicates based on the values in the first and second columns, and the first row is a header row.

Explanation

To illustrate how the Range.RemoveDuplicates method works, let’s use a scenario with real numbers. Suppose you have the following data in a worksheet:

You want to remove the duplicate rows based on the product column, and keep the last time stamp for each product. To do this, you can use the following VBA code:

Sub RemoveDuplicateRange()
    'Define the bigger range to remove duplicates from
    Dim rng As Range
    Set rng = Range("A1:C6")
    
    'Specify the column to check for duplicate values
    Dim col As Long
    col = 2 'The product column is the second column
    
    'Specify that the data has a header row
    Dim hdr As XlYesNoGuess
    hdr = xlYes 'The first row is a header row
    
    'Sort the data by the time column in descending order
    'This will ensure that the last time stamp for each product is kept
    rng.Sort Key1:=rng.Columns(1), Order1:=xlDescending, Header:=hdr
    
    'Remove the duplicate rows based on the product column
    rng.RemoveDuplicates Columns:=col, Header:=hdr
End Sub

After running this code, the worksheet will look like this:

As you can see, the duplicate rows based on the product column have been removed, and the last time stamp for each product has been kept.

Other approaches

There are other ways to remove duplicate range in a bigger range in excel formula using VBA. For example, you can use the following methods:

• Use the AdvancedFilter method to filter the unique values in the bigger range, and copy them to another location. For example, Range("A1:C6").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("E1"), Unique:=True will copy the unique values from A1 to C6 to E1 and below.
• Use the Dictionary object to store the unique values in the bigger range, and write them back to the worksheet. For example, Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary"): For Each cell In Range("A1:C6").Columns(2): dict(cell.Value) = cell.Value: Next: Range("A1:C6").ClearContents: Range("A1").Resize(dict.Count, 3).Value = Application.Transpose(Array(dict.Keys, dict.Items, dict.Items)) will store the unique values from the second column in a dictionary, and write them back to the worksheet with the same price for each product.
• Use the CountIf function to count the number of occurrences of each value in the bigger range, and delete the rows that have more than one occurrence. For example, Dim k As Long: Dim Lr As Long: Lr = Cells(Rows.Count, 1).End(xlUp).Row: For i = 1 To Lr: k = Application.WorksheetFunction.CountIf(Range("A1:A" & i), Range("A" & i)): If k > 1 Then Range("A" & i).EntireRow.Delete: End If: Next will count the number of occurrences of each value in the first column, and delete the rows that have duplicates.