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.

Option Explicit

Sub StartEnd() 'Use Excel VBA Autofilter function to isolate data between Dates
Dim StDate As Long: StDate=[J10]
Dim EndDate As Long: EndDate=[K10]

Range("F10:F21").AutoFilter 1, ">=" & StDate, xlAnd, "<=" & EndDate
End Sub

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.

Sub SingleDate() 'Autofilter for a single date with VBA
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

End Sub