use a range variable in advanced filter

[Jake]

Hi,
I have a variable range that I would like to filter for unique values. I am
having trouble defining the range and get a runtime error 1004, reference is
not valid.
I copy the field from a database to another sheet and would like to apply
the filter to just that range to get the uniquie values.
Code:
Sub FilterUniqueValues()
Dim CostCentre As Range
Sheets("Data").Select 'Data sheet containing database
Range("C1").Select 'top cell of field to copy
Range(Selection, Selection.End(xlDown)).Select 'select field
Selection.Copy
Sheets("Criteria").Select 'move to second sheet
Range("M2").Select 'paste cell for copied field
ActiveSheet.Paste
Application.CutCopyMode = False
Set CostCentre = Selection 'set range to coped field
'filter for unique values
CostCentre.AdvancedFilter xlFilterCopy, "I2:I3", "o2", True
End Sub

thanks for any help
Jake

 

[Dave Peterson]

I'm not quite sure what you're doing, but that .advancedfilter needs ranges
passed to it:

With Sheets("criteria")
CostCentre.AdvancedFilter Action:=xlFilterCopy, _
criteriarange:=.Range("I2:i3"), _
copytorange:=.Range("o2"), unique:=True
end with

 

[crferguson]

Hi,
I have a variable range that I would like to filter for unique values.  I am
having trouble defining the range and get a runtime error 1004, reference is
not valid.
I copy the field from a database to another sheet and would like to apply
the filter to just that range to get the uniquie values.
Code:
Sub FilterUniqueValues()
    Dim CostCentre As Range
    Sheets("Data").Select   'Data sheet  containing database
    Range("C1").Select      'top cell of field to copy
    Range(Selection, Selection.End(xlDown)).Select 'select field
    Selection.Copy
    Sheets("Criteria").Select 'move to second sheet
    Range("M2").Select 'paste cell for copied field
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Set CostCentre = Selection  'set range to coped field
    'filter for unique values
    CostCentre.AdvancedFilter xlFilterCopy, "I2:I3", "o2", True
End Sub

thanks for any help
Jake

Why not just filter the values in the database before copying them
over? Instead of something like "SELECT CostCenter FROM tEmployees"
use the Distinct keyword to get only unique values, i.e.: "SELECT
DISTINCT(CostCenter) AS CostCenters FROM tEmployees"

 

[Jake]

Thanks Dave, that's works!
I want to get only the unique values from a field in a database. Perhaps
there is a better way, but this works.
Jake

 

[Dave Peterson]

I wasn't sure what you were doing when you pasted the values in M2.

This creates a unique list:

Option Explicit
Sub FilterUniqueValues2()
Dim CostCentre As Range
Dim LastRow As Long

With Worksheets("Data")
LastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
Set CostCentre = .Range("C1:C" & LastRow)
End With

With Sheets("criteria")
CostCentre.AdvancedFilter Action:=xlFilterCopy, _
copytorange:=.Range("o1"), unique:=True
End With

End Sub

The list is pasted into O1 (header) of the Criteria worksheet.