using advanced filter in VBA with a dynamic range

[Guest]

I recorded the following macro, which applies the Advanced Filter to a range
of data. This range of data changes from month to month, in terms of the
number of rows of data. The criteria range and the number of columns do not
change. How can I modify it to automatically adjust the filter range?

Here's the code:

Sub AdvFilterTest()
'
' AdvFilterTest Macro
' Macro recorded 10/2/2006 by df78700
'

'
Range("A5:S42207").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:= _
Range("U1:U5"), CopyToRange:=Range("V1"), Unique:=False
End Sub


Basically, the thing that would change from month to month is the cell in
column S of the first range given.

Hope I've made this clear.

Thanks,

Dave

 

[Guest]

Try:
Range("A5:S" & Range("S" & Rows.Count).End(xlUp))