PDA

View Full Version : how to filter all dates between two dates



emmr
08-22-2019, 11:38 PM
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

mana
08-23-2019, 04:02 AM
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

p45cal
08-23-2019, 05:38 AM
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.

emmr
08-23-2019, 09:34 AM
Dear mana (http://www.vbaexpress.com/forum/member.php?61551-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

snb
08-23-2019, 10:14 AM
Haven't you got a macrorecorder ?
Haven't you ever used autofilter ?