use a range variable in advanced filter
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
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
--
Dave Peterson
|