Find lowest date in filtered list

[shadow12345]

Hi,

I am creating a filtered list using the autofilter. I would like to know how I can select the lowest date in column B in the filter.

I tried setting it but every month it becomes a different month and stops working.

Any Ideas anyone?

 

[jasonb75]

Try

=SUBTOTAL(5,B1:B100)

Change the range to match yours.

 

[shadow12345]

How could I apply that to my filter?

I am setting it using this.

Sub Macro2()
'
' Macro2 Macro
'

'
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$AH$841").AutoFilter Field:=2, Criteria1:= _
        "2011-04-20"
    Range("B2").Select
End Sub

 

[jasonb75]

Sorry, I misread your question, thought you wanted to return the lowest date after the data had been filtered.

Try

Sub Macro2()
    ActiveSheet.Range("A1:AH841").AutoFilter Field:=2, Criteria1:="1", Operator:=xlBottom10Items
End Sub

 

[shadow12345]

I get run time error 1004. Auto filter method of range class failed.

 

[jasonb75]

is the correct sheet active?

are the dates in column B in a valid excel format?

That error should only happen if there is not at least 1 valid date in column B

 

[shadow12345]

hmmmm stupid thing... i have to f2 and enter in each cell to get it to come out as a date.

 

[jasonb75]

Select the whole list,

Data > Text to columns > Finish ** Don't click Next, just Finish **

That will do them all at once.

 

[shadow12345]

oh I like that.... much faster.

 

[Gerald Higgins]

The way I would do this, probably not the best way, is to have a cell somewhere that shows what the minimum value is, using
=MIN()
and use that to drive the setting of the filter criteria.