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:
[VBA]
Set SrcRange = Range(ActiveSheet.Range(ListOfVendors))
[/VBA]would be:
[VBA]
Set SrcRange = Activesheet.Range(ListOfVendors)
[/VBA]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:

[VBA]
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
[/VBA]
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 where you have also had help, please read the link in my signature with regards to cross posting!