Consulting

Results 1 to 5 of 5

Thread: how to filter all dates between two dates

  1. #1
    VBAX Regular
    Joined
    Aug 2019
    Posts
    14
    Location

    how to filter all dates between two dates

    Hello Everyone,
    in my database column C is Date Column, how can i filter/select all dates between 10-January-2019 to 23-January-2019 with two clicks
    & how i can do this through VBA

    TIA

  2. #2
    VBAX Expert
    Joined
    Sep 2016
    Posts
    788
    Location
    A1: 10-January-2019
    A2: 23-January-2019


    Option Explicit
    
    
    Sub test()
        Dim r As Range
        Dim d1 As Long
        Dim d2 As Long
        
        With Sheets("sheet1")
            .Activate
            Set r = .Range("D1", .Range("D" & Rows.Count).End(xlUp))
            d1 = .Range("a1").Value2
            d2 = .Range("a2").Value2
        End With
    
    
        r.Worksheet.AutoFilterMode = False
        r.AutoFilter 1, ">=" & d1, xlAnd, "<=" & d2
        If r.SpecialCells(xlCellTypeVisible).Count > 1 Then
            Intersect(r, r.Offset(1)).Select
        Else
            r.AutoFilter
            r(1).Select
        End If
        
    End Sub

  3. #3
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    If your 'database' is a table then you can use a slicer (unfortunately not a Time Slicer); click on the first date, hold the shift key down and click on the second date.[Sheet4 cell G1]
    Lower down on the sheet I've added a pivot table and a Time Slicer where you can do it in one click: click and drag the days you want to filter for. This pivot table works like a plain filter because there is a unique column of data at the left of the pivot, otherwise it would do what pivot tables are good at which is summarising/aggregating/grouping data rows.[Sheet4 cell E45]
    Using VBA, on Sheet2 click on one date then another date in column C and it will filter between those two dates. Keep on clicking (in pairs of clicks) to filter again. You will at one point have to clear the filter to see the dates again. You can click the dates in any order (earlier date/later date). The code is in Sheet2's code-module.
    Attached Files Attached Files
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  4. #4
    VBAX Regular
    Joined
    Aug 2019
    Posts
    14
    Location
    Dear mana thx a lot its working, i need one more filter with this could u plz help for that, like in E column country name are listed, after filtering the date if i want to filter country than what would be the code. i want to write the country name in A3 and that will be filtered in column E. tnx

  5. #5
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    Haven't you got a macrorecorder ?
    Haven't you ever used autofilter ?

Posting Permissions

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