find the first cell in a list of dates for January
 

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)

--
Regards,
Tom Ogilvy


"Larry Levinson" wrote in message
...
In column A I have a list of dates, arranged in descending order, most
recent to oldest. I need to know where one year starts and another
ends to do a year_to_date calculation. I can test each cell for
datevalue being more than Jan. 1, but I would like to save cycles by
going to the end of January first and THEN comparing the datevalue of
the active cell ... also, I can not be sure that I have an entry for
each CALENDAR day ... (so, no, I can't just search for Jan. 31. By the
same token, I can't just search for Jan. 1)

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

find the first cell in a list of dates for January
 

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 ....)

find the first cell in a list of dates for January
 

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 ....)

find the first cell in a list of dates for January
 

You are a star ... but, um, turning it 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 ....)

find the first cell in a list of dates for January
 

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 ....)

find the first cell in a list of dates for January
 

TOGGLE OFF
Selection.AutoFilter

but, `sendkeys'?


Larry Levinson wrote:

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 ....)

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

find the first cell in a list of dates for January
 

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 ....)