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 :)
The second part of this video explains the same technique for autofiltering between dates. File to go with the YouTube video is here:
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 data set. It will filter the data between 1 Jan and 4 March (global date system).
As you can see from above the data is filtered between the Start Date and End Date at the right of screen in the red. There are named ranges which look at both the START and the END date. Be sure to remember this if you are using the procedure in your own workbook. Include both a named range for the start and end date or put a cell reference for those dates.
The above shows the data after the VBA date autofilter procedure has run. Only dates between the specified dates can be seen.
The following is the Excel VBA code to check 2 dates and filter the results.
Sub StartEnd() 'Use Excel VBA Autofilter function to isolate data between Dates
Dim StDate As Long
Dim EndDate As Long
StDate = [Start]
EndDate = [End]
In the above a range called Start has the StDate and a range called End has the EndDate. 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 And Delete the results
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
sh.[A1].AutoFilter
End Sub
The following Excel file will go through the procedure to autofilter between dates. Hope it helps.
