Dim rng as Range, rng1 as Range
set rng = Activesheet.Autofilter.Range.columns(1).Cells
set rng = rng.offset(1,0).Resize(rng.rows.count-1)
On error resume next
set rng1 = rng.Specialcells(xlVisible)
On Error goto 0
if not rng1 is nothing then
rng1(1).Select
else
msgbox "No rows visible"
End if

--
Regards,
Tom Ogilvy



"Larry Levinson" wrote in message
...
sigh ... two more problems and then I promise to go away ...


i want to move to the first row in the resulting list, just like arrow
down would do, but the offset only moves me to row 2 instead of the
beginning of the filtered list .. and I don't see how to toggle the
autofilter off ...


"Tom Ogilvy" wrote:

ActiveSheet.Range("A1").AutoFilter _
field:=1, _
Criteria1:="<" & clng(year_start)
' VisibleDropDown:=False
or

ActiveSheet.Range("A1").AutoFilter _
field:=1, _
Criteria1:="<" & year_start

' VisibleDropDown:=False



"Larry Levinson" wrote in message
.. .
what about toggling autofilter on and off, ala

Dim year_start As Date
year_start = DateValue("1/1/" & Year(Date))

ActiveSheet.Range("A1").AutoFilter _
field:=1, _
Criteria1:="year_start"
' VisibleDropDown:=False


my current problem is getting the criteria to be `less than'
year_start instead of just equal to year_start.

(and any insight you might have in toggling off the autofilter would
be good too ... ) thanks.





"Tom Ogilvy" wrote:

there isn't any magic way to know the date contained in the cell.

You could use a dummy column with a formula

=if(Year(a1)<Year("a2"),na(),"")

copy this down your dummy column. This marks the first date in each
new
year.

then
set rng = Columns(4).SpecialCells(xlFormulas,xlErrorvalues)

Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)


Larry Levinson
Talking up to the vocal ...
LLevinson*Bloomberg.net
(remove the star etc ....)