Creating dynamic multi (and variable) column remove duplicates in VBA

[wcurran]

Using Excel 2010 on a Windows 7 machine. Trying to create code to allow a user to hit a button and have the data remove duplicates for the data selected. For example, if the columns are WBS, Labor Category, and Name, the user can select any combination of those three to produce a listing of the associated data with no duplicates. In my case, I have 8 columns so the 'Remove duplicates' has to adjust dynamically to the columns selected by the user.

I'm close but I get a Run-time error 1004: Application-defined or object-defined error.

My code:
Sub VolTable_SelectAndSort()
Dim Calc_Range As Variant
Dim CLIN, WBS, Co, Ref, LCat, ResName, BU, FSS, StartRange, EndRange As Range
Dim CalcRng1, CalcRng2, CalcRng3, CalcRng4, CalcRng5, CalcRng6, CalcRng7, CalcRng8, ctr As Integer
Dim StartCtr, EndCtr, CalcEnd, ArraySlots As Integer
'
' Determines the data to use
'
ArraySlots = 0
StartCtr = 0
EndCtr = 0
Set CLIN = Range("B3")
If CLIN = "Include" Then
CalcRng1 = 1
Calc_Range = Array(CalcRng1)
End If
Set WBS = Range("C3")
If WBS = "Include" Then
CalcRng2 = 2
ArraySlots = ArraySlots + 1
End If
Set Co = Range("D3")
If Co = "Include" Then
CalcRng3 = 3
ArraySlots = ArraySlots + 1
End If
Set FSS = Range("I3")
If FSS = "Include" Then
CalcRng4 = 4
ArraySlots = ArraySlots + 1
End If
Set Ref = Range("E3")
If Ref = "Include" Then
CalcRng5 = 5
ArraySlots = ArraySlots + 1
End If
Set BU = Range("F3")
If BU = "Include" Then
CalcRng6 = 6
ArraySlots = ArraySlots + 1
End If
Set LCat = Range("G3")
If LCat = "Include" Then
CalcRng7 = 7
ArraySlots = ArraySlots + 1
End If
Set ResName = Range("H3")
If ResName = "Include" Then
CalcRng8 = 8
ArraySlots = ArraySlots + 1
End If
Calc_Range = Array(CalcRng1, CalcRng2, CalcRng3, CalcRng4, CalcRng5, CalcRng6, CalcRng7, CalcRng8)
Range("BA6:BH210").Select
selection.Copy
Range("BK6").Select
selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

ActiveSheet.Range("BK6:BR210").RemoveDuplicates Columns:=(Calc_Range), Header:=xlNo
Range("F2").Select
End Sub



Thanks!!

 

[JLGWhiz]

Is there a reason why using the "Remove Duplicates" button on the ribbon wouldn't work?

 

[wcurran]

The model is pretty well locked down and the people using it may not understand how the remove duplicates function works. Trying to automate it so that they can make their selections then hit a button to do the rest. Thanks!

 

[JLGWhiz]

I found this link that you might want to read before pursuing the route you are going.

Erlandsen Data Consulting » Blog Archive » Remove Duplicate Items From A Worksheet Range

 

[wcurran]

I will absolutely take a look - Thanks!!