VBA Filter on Date

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: VBA Filter on Date

  1. #1
    New Member
    Join Date
    Sep 2008
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Inactive
    Richard Schollar's Avatar
    Join Date
    Apr 2005
    Location
    UK
    Posts
    23,701
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter on Date

    Genius!!! Thank you!

  4. #4
    Board Regular
    Join Date
    Nov 2011
    Posts
    111
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  5. #5
    New Member
    Join Date
    Jul 2016
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Filter on 2 dates entered by user

      
    Hello everyone,

    I have a macro which I want to filter data against 2 dates which the user enters. Any ideas on how to resolve this?

    User will enter 2 dates in Input Box and macro should filter date basis 2 dates entered. Dates are stored in column D of worksheet "Copy"

    I am in urgent need of same so please assist on same quickly.

    Thanks for your help in advance!!
    Last edited by RPM1979; Jul 25th, 2017 at 07:44 AM.

User Tag List

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