Results 1 to 4 of 4

VBA Filter on Date

This is a discussion on VBA Filter on Date within the Excel Questions forums, part of the Question Forums category; Hello everyone, I have a macro which I want to filter data against a date which the user enters then ...

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    15

    Default VBA Filter on Date

    Hello everyone,

    I have a macro which I want to filter data against a date which the user enters then delete the visible rows however when I run this, it doesn't return anything in the filter so I think it is not recognising the criteria as a date. Any ideas on how to resolve this?

    Thanks for your help!!


    Sub Delete_old()

    Dim CutOffDate As Date

    CutOffDate = Application.InputBox("Enter the cut off date in the Format ""DD/MM/YYYY""")

    Selection.AutoFilter
    Selection.AutoFilter Field:=17, Criteria1:="<=" & CutOffDate
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
    Range("A1").Select

    End Sub

  2. #2
    MrExcel MVP
    Moderator
    Rediscovering the love
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,666

    Default Re: VBA Filter on Date

    Hi

    try this:

    Code:
    Sub Delete_old()
    
    Dim CutOffDate As Long
    Dim temp As String
    
     temp = Application.InputBox("Enter the cut off date in the Format ""DD/MM/YYYY""")
     
    CutOffDate = CLng(DateValue(temp))
    
    Selection.AutoFilter
    Selection.AutoFilter Field:=17, Criteria1:="<=" & CutOffDate
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter
    Range("A1").Select
    
    End Sub
    Richard Schollar

    Using xl2013

  3. #3
    New Member
    Join Date
    Sep 2008
    Posts
    15

    Default Re: VBA Filter on Date

    Genius!!! Thank you!

  4. #4
    Board Regular
    Join Date
    Nov 2011
    Posts
    107

    Default Re: VBA Filter on Date

    Hi I am using the below code to filter data between. The issue i am facing is , when data is entered to any cell in the workbook the filters refresh happens. I am looking for teh filter to refresh only when the data in the cell C1 or D1 is changed.

    Private Sub Worksheet_Change(ByVal Target As Range)


    Dim StartDate As Long
    Dim EndDate As Long



    StartDate = Range("C1").Value
    EndDate = Range("D1").Value


    Range("A3").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=6, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate






    End Sub

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com