issue with autofiltering on a Date criteria

[cirugio]

I am trying to create vba code which will automatically filter on a specific currency = 'GBP' (column 16) and where the maturity date (column 7) is less then today's date. The code does the filtering for the currency but not for the date criteria. Am at a point where I am just spinning my wheels. Hoping someone can assist in determining what I am doing wrong. Thank you for your time in advance.

    Range("A1:Q1").Select
    Selection.AutoFilter
    
    ActiveSheet.Range("$A$1:$Q$1000000").AutoFilter Field:=16, Criteria1:="GBP"
    ActiveSheet.Range("$A$1:$Q$1000000").AutoFilter Field:=7, Criteria1:= _
     "<" & Now(), Operator:=xlAnd

 

[dmt32]

Hi,
see if this does what you want:

With ActiveSheet.Range("A1:Q" & Cells(Rows.Count, "Q").End(xlUp).Row)
    .AutoFilter
    
    .AutoFilter Field:=16, Criteria1:="GBP"
    .AutoFilter Field:=7, Criteria1:="<" & CLng(Date)
    
End With

Dave

 

[cirugio]

This worked beautifully. Thank you Dave :0)
I do have a question though on the CLng(Date) portion of the code. Can you tell me why the date has to be converted to a long integer? Is it because the Date function may have time stamp? Just trying to understand. Thanks again.

Hi,
see if this does what you want:

With ActiveSheet.Range("A1:Q" & Cells(Rows.Count, "Q").End(xlUp).Row)
    .AutoFilter
    
    .AutoFilter Field:=16, Criteria1:="GBP"
    .AutoFilter Field:=7, Criteria1:="<" & CLng(Date)
    
End With

Dave

 

[dmt32]

This worked beautifully. Thank you Dave :0)
I do have a question though on the CLng(Date) portion of the code. Can you tell me why the date has to be converted to a long integer? Is it because the Date function may have time stamp? Just trying to understand. Thanks again.

Date function returns just the current system Date. Now function returns the Current system Date & Time.

A Date on its own is, or is like a Long Integer - As Excel sees the dates as a US date & way to ensure that Autofilter returns correct result is to just coerce value to a long integer.

When using Autofilter with dates, I normally pass them to a Long variable using DateSerial Function & use this to apply in the filter but sometimes the suggestion given can work ok.

Dave

 

[cirugio]

Thanks for clarifying and making it more efficient. Nice not to have to put the row numbers. Thanks again!!

Date function returns just the current system Date. Now function returns the Current system Date & Time.

A Date on its own is, or is like a Long Integer - As Excel sees the dates as a US date & way to ensure that Autofilter returns correct result is to just coerce value to a long integer.

When using Autofilter with dates, I normally pass them to a Long variable using DateSerial Function & use this to apply in the filter but sometimes the suggestion given can work ok.

Dave