PDA

View Full Version : Solved: Relative range for advanced filter



yasarayhanka
08-29-2007, 08:21 AM
I am trying to use advanced filter function for a relative range that will change everyday.
does anyone know how to do it?
Thanks,

Yasar

lucas
08-29-2007, 08:24 AM
how does it change every day? Can you use a named range, it will not chnage if rows are deleted etc.

yasarayhanka
08-29-2007, 08:36 AM
how does it change every day? Can you use a named range, it will not chnage if rows are deleted etc.

there will be new rows added to it,
Here is the code I am using

Range("G1:G12491").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Workbooks("test.xls").Sheets("Sheet2").Range("A1:A5"), _
Unique:=False
and the range tomorrow might be ("G1:G13000")
With other functions I can write "selection" at the beginning of the code and it will work like sort, function, but with this I could not manage it so far.

lucas
08-29-2007, 08:48 AM
Have you tried:

Range("G:G").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Workbooks("test.xls").Sheets("Sheet2").Range("A1:A5"), _
Unique:=False

lucas
08-29-2007, 08:51 AM
this seems to work with a selection but I don't have your data so untested also:
With Selection
.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Workbooks("test.xls").Sheets("Sheet2").Range("A1:A5"), _
Unique:=False
End With

yasarayhanka
08-29-2007, 10:55 AM
this seems to work with a selection but I don't have your data so untested also:
With Selection
.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
Workbooks("test.xls").Sheets("Sheet2").Range("A1:A5"), _
Unique:=False
End With

thank you very much, that saved the day