On Jan 4, 1:58*pm, Jake wrote:
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"