Advanced Filter VB Script for Variable Criteria Range

[Guest]

I'm new to scripting within Excel, and I am wondering if it is possible to
write a script for an Advanced Filter where the Criteria Range is itself a
variable. I would like to filter the data in a spreadsheet with the data in
a single column of another sheet. Since the amount of data in this column
can change from time to time and selecting the entire column incudling blank
cells below the data doesn't work, I want to have the filter adjust
accordingly. Example script:

Range (.Cells). AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Sheets ("Sheet1") .Range(VARIABLE RANGE HERE), Unique:=False

Thanks!

 

[Ron de Bruin]

Hi Jason

You can use a dynamic range name
http://www.contextures.com/xlNames01.html#Dynamic

 

[Peter Huang [MSFT]]

Hi Jason,

You may try to use the code below.

Different rg value will do different Filter and get different result.

Sub Macro1()
Dim rg As Range
'Set rg = Sheets("Sheet1").Range("C1:C2")
Set rg = Sheets("Sheet1").Range("D12")
Range("A1:A16").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
rg, Unique:=False
End Sub


Best regards,

Peter Huang

Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.