Excel VBA - Autofilter a date column on a set of date

Question

I am still learning Excel VBA and would appreciate it if anyone can help me get this right. I want to filter a date column based on a startDate and endDate I specified in a sheet called DateMaster. When I run the macro, it gives me Run Time Error 13, Start Date = 0. Below is my code.

Sub FIlterCopy()

Dim StartDate As Long
Dim EndDate As Long

StartDate = ThisWorkbook.Worksheets("DateMaster").Range("C2").Value
EndDate = ThisWorkbook.Worksheets("DateMaster").Range("D2").Value

Application.ScreenUpdating = False

ThisWorkbook.Worksheets("FilterMaster").Activate

Range("A:BA").Select

Selection.ClearContents

Application.Workbooks.Open ("C:\WRI\Data\Revenue Update.xls")

Windows("Revenue Update.xls").Activate

Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter
Selection.AutoFilter Field:=5, Criteria1:= _
"=Backlog", Operator:=xlOr, Criteria2:="=RMA"
Selection.AutoFilter Field:=29, Criteria1:= _
"=Direct"
Selection.AutoFilter Field:=20, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<=" & EndDate

Application.ScreenUpdating = True

End Sub

Have you tried stepping through your code using F8? Also, I would avoid selecting and activating in your code. This post explains how. You are getting this error though because it is a type mismatch. — Dan
(1) If you want to store a date in a variable then you might want to save it as such: Dim StartDate As Date. (2) The error message is pretty obvious. Why don't you check if cell C2 truly contains a valid date. (3) Even if there is a date you might want to confirm by adding: If IsDate(ThisWorkbook.Worksheets("DateMaster").Range("C2").Value) Then just before assigning the date and a Debug.Print StartDate just after assigning the variable. — Ralph

Diego Ribba Diego Ribba · Accepted Answer · 2017-02-08T14:31:59

If you set StartDate and EndDate as Long, you should use the CDate command at autofilter

Selection.AutoFilter Field:=20, Criteria1:=">=" & CDate(StartDate), Operator:=xlAnd, Criteria2:="<=" & CDate(EndDate)

Otherwise, define both variables as Date.

Excel VBA - Autofilter a date column on a set of date

1 Answers