use a range variable in advanced filter
 

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

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

use a range variable in advanced filter
 

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"

use a range variable in advanced filter
 

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" wrote:

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

use a range variable in advanced filter
 

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.

Jake wrote:

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" wrote:

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


--

Dave Peterson