Autofilter Between Dates VBA
The autofilter in Excel can be used with VBA to isolate dates. It is not as intuitive to use the autofilter on dates as I tried and failed many times before finding a method which worked for me. I am sure there is a more efficient way out there to use the autofilter to filter dates but I am not aware of it as yet. So the following Excel example is the way I currently get the job done. If you have something to add do email me :)
Being able to autofilter on more than 2 criteria including dates is important lesson to learn. It further avoids iterations when you need to test conditions involving dates. The following an example is of a small dataset.
As you can see from above the data is filtered between the Start Date and End Date at the right of screen in BLUE. This is the VBA code to achieve the task.
Sub StartEnd() 'Use Excel VBA Autofilter function to isolate data between Dates
Dim StDate As Long: StDate=[J10]
Dim EndDate As Long: EndDate=[K10]
In the above J10 has the start date and K10 has the end date. The following file is an Excel example of the above diagram.
While the following article is an Excel example of the same method using the Advanced Filter. Advanced Filter Between Dates. Which ever Excel method works for you.
Autofilter a Single Date
There may be a situation where you would only want to filter one date and you have that date in a single cell. In this situation here is a suggested solution with the criteria on another sheet.
Dim StDate As Long
Dim rng As Range
Dim sh As Worksheet
StDate = Sheet2.[K1] 'range where date lives
Set sh = Sheet1
Set rng = sh.Range("A1:A" & sh.Cells(Rows.Count, 1).End(xlUp).Row)
rng.AutoFilter 1, ">=" & StDate, xlAnd, "<=" & StDate
If sh.[A500].End(xlUp) > 1 Then rng.Offset(1).EntireRow.Delete