none
VBA - Filter values that are <= to TODAY RRS feed

  • Question

  • Hi :)

    I have got a filter that I need to act on a column of dates. This purpose of it is to filter out the values that are greater than Today's date e.g. today = 11/06/2015, I need to rows where the date is 12/06/2015 and above.

    I am currently trying this via the below script;

    Dim zLstrw as Long

    Dim iDate as string

    iDate = Date

    zLstrw = Cells(Rows.Count,"C").End(xlUp).Row    'to ID the last row of data in column C

    Range("$A$1:$G$" & zLstRw).AutoFilter field:=5, Criteria1:="<=" & iDate

    I have also tried using ADate and BDate as below

    Dim ADate as Date

    Dim BDate as long

    ADate = Date

    BDate = ADate

    Is there anything (or everything) that you can see me doing wrong here as currently the results of the filter leave the sheet blank.

    Thanks

    Kai

    Thursday, June 11, 2015 1:04 PM

All replies

  • Found it. Not sure why I didn't see this earlier, but it seems to work. (This also includes my other filter - FYI)

    Dim zLstRw As Long, zRw As Long
    Dim iDate As Date
    Dim jDate As Long

    iDate = Date
    jDate = iDate

    Sheets("Med Rev Fut Att").Select

    zLstRw = Cells(Rows.Count, "C").End(xlUp).Row
    Range("$A$1:$G$" & zLstRw).AutoFilter field:=7, Criteria1:="<>"
    Rows("2:" & zLstRw).Delete
    Cells.AutoFilter

    Columns("E:E").NumberFormat = 0
    Range("$A$1:$G$" & zLstRw).AutoFilter field:=5, Criteria1:="<" & jDate
    Rows("2:" & zLstRw).Delete
    Cells.AutoFilter

    Thursday, June 11, 2015 1:33 PM
  • An easy way to answer this is to record a macro of you manually filtering for today's date. Then edit teh recorded code to produce teh same result but based on today's date instead.

    Rod Gill
    Author of the one and only Project VBA Book
    www.project-systems.co.nz

    Friday, June 12, 2015 3:15 AM