Extracting Unique Values from a Column

[Rabi]

Hi everybody

A small piece of my following code seems plaqued with a number of errors.






Private Sub CopyUniqueVals(SrcRng As Range, DestRng As Range)
Range(SrcRng).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(DestRng), Unique:=True
End Sub




Sub TestCopyUniqueVals()

Dim SrcRange As Range, DestRange As Range




Sheets("SubCon").Activate


' The following statement reports "Application-defined or object-defined error:


Set SrcRange = Range(ActiveSheet.Range(ListOfVendors))



' The following statement reports "Method 'Range' of object '_Global' failed:

' The Range("C4:C614") is the same as ListOfVendors named range.

Set SrcRange = Range(ActiveSheet.Range("C4:C614"))
Set DestRange = Range(Sheets("Summary").Range("A15"))


' The following statement reports "ByRef argument type mismatch":
' Even if I use the same arguments ByVal, it reports "Argument type mismatch".
Call CopyUniqueVals(SrcRange, DestRange)






End Sub



' The following procedure lists the first vendor twice whereas; others are unique:



Private Sub CopyUniqueVals2()

Sheets("SubCon").Activate



Range("C4:C614").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Z4"), Unique:=True



End Sub





Thanks for any tip, in anticipation, for pointing out the mistakes and their remedy.

Regards,

[Simon Lloyd]

The namining convention for this line:

Set SrcRange = Range(ActiveSheet.Range(ListOfVendors))
would be:

Set SrcRange = Activesheet.Range(ListOfVendors)
so will your other ranges need changing to the correct format, as for you unique lists i don't know, can you post a sample workbook?

[Rabi]

Thanks Simon for the tip. But, I am still having problem with the modified code, as below:


Private Sub CopyUniqueVals(Src As Range, Dest As Range)
Sheets("SubCon").Activate

' The following statement works fine:
' Range("C4:C614").AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(Dest), Unique:=True

' But the following statement reports the error "Application-defined or object-defined error"
ActiveSheet.Range(Src).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range(Dest), Unique:=True
End Sub

Sub TestCopyUniqueVals()
Dim SrcRange As Range, DestRange As Range

Sheets("SubCon").Activate

Set SrcRange = ActiveSheet.Range("C4:C614")
Set DestRange = Sheets("Summary").Range("A15")

Call CopyUniqueVals(SrcRange, DestRange)
End Sub

Sorry for not understanding well. Further assistance is highly appreciated.

Regards,

[Simon Lloyd]

Rather than post parts of your code post it in it's entirity, or better still post a sample workbook - what you may think is the problem may not be the cause!

[Simon Lloyd]

Cross posted today here (http://www.excelforum.com/showthread.php?p=1924979#post1924979) where you have also had help, please read the link in my signature with regards to cross posting!