Erlandsen Data Consulting

According to the Excel Developer Reference for Office 2007 documentation, or well hidden in the built-in the VBA help file, you should be able to delete duplicate entries from a worksheet range using the RemoveDuplicates method like this:

ActiveSheet.Range("A1:F100").RemoveDuplicates

This method is new in Excel 2007 and will not work in older Excel versions. It takes 2 optional arguments (columns and headers) and should work without any of them, but this seems not to be true. If you use the method and pass values for the 2 arguments it works like intended.
Both of the example lines below works just fine:

ActiveSheet.Range("A1:F100").RemoveDuplicates Columns:=Array(1,2,3,4,5,6), Header:=xlYes
ActiveSheet.Range("A1:F100").RemoveDuplicates Array(1,2,3,4,5,6), xlYes

The problem is that sometimes you don’t know exactly how many columns you want to check duplicates in, e.g. when you write a procedure for removing duplicates from a worksheet where the number of columns with data may vary from time to time.
Then you need to replace Array(1,2,3,4,5,6) in the example above with a variable that contains a similar zero-based Variant array where the upper bound value is one less than the number of columns you want to check for duplicates, e.g. like this:

Dim varItems(0 to 2) As Variant ' 3 columns
Dim varItems(0 to 5) As Variant ' 6 columns

Below is a procedure example that can be used for removing duplicates from any sized range:

Sub RemoveDuplicatesFromRange(objRange As Range, _
    Optional varColumns As Variant = False, _
    Optional blnHasHeader As Boolean = True)
' varColumns must be a 0-based variant array
Dim lngCount As Long, i As Long
    If objRange Is Nothing Then Exit Sub
    
    With objRange
        If Not IsArray(varColumns) Then ' check all columns in the range
            ReDim varColumns(0 To .Columns.Count - 1) ' must be a 0-based variant array
            For i = 1 To .Columns.Count
                varColumns(i - 1) = i
            Next i
        End If
        On Error GoTo FailedToRemoveDuplicates
        If blnHasHeader Then
            .RemoveDuplicates (varColumns), xlYes
        Else
            .RemoveDuplicates (varColumns), xlNo
        End If
        On Error GoTo 0
    End With
    Exit Sub
    
FailedToRemoveDuplicates:
    If Application.DisplayAlerts Then
        MsgBox Err.Description, vbInformation, "Error Removing Duplicates From Range: " & objRange.Address
    End If
    Resume Next
End Sub

And below you will find a few examples on how to use the procedure above:

Sub TestRemoveDuplicatesFromRange1()
    RemoveDuplicatesFromRange Range("A1").CurrentRegion ' checks all columns
End Sub

Sub TestRemoveDuplicatesFromRange2()
    RemoveDuplicatesFromRange Range("A1").CurrentRegion, Array(1, 3, 5) ' checks columns 1,3 and 5
End Sub

Sub TestRemoveDuplicatesFromRange3()
Dim varItems(0 To 2) As Variant ' must be 0-based variant array
    varItems(0) = 1
    varItems(1) = 3
    varItems(2) = 5
    RemoveDuplicatesFromRange Range("A1").CurrentRegion, varItems ' checks columns 1,3 and 5
End Sub

Sub TestRemoveDuplicatesFromRange4()
Dim lngCount As Long, varItems() As Variant, i As Long, c As Long
    lngCount = Range("A1").CurrentRegion.Columns.Count \ 2
    ReDim varItems(0 To lngCount - 1) ' must be 0-based variant array
    i = 0
    For c = 1 To Range("A1").CurrentRegion.Columns.Count Step 2
        varItems(i) = c
        i = i + 1
    Next c
    RemoveDuplicatesFromRange Range("A1").CurrentRegion, varItems ' checks every odd column
End Sub