Selected Answer
To properly program an InputBox, as you are attempting to do, is surprisingly complicated. Perhaps the reason is that it hasn't been changed much since the turn of the century. Yet, it isn't difficult but one has to be precise.
Key is to understand that the InputBox function returns a variant of string type. This means that if you enter nothing it's a null string (a string of zero length but not zero). Otherwise, since it's a variant, it can be anything. A date is of particular difficulty because it's a string by nature but a number by Excel's use. CDate(DateRange) will instruct VBA to convert the "variant of string type" to a "variant of date type). After that you can treat it as a date.
Sub filtdat()
Dim Lastrow As Long
Dim DateRange As Variant
Lastrow = Columns("A:A").Find(What:="*", SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Row
Do
DateRange = InputBox("Please enter effective date (DD/MM/YYYY)")
If DateRange = "" Then Exit Sub
If IsDate(DateRange) Then
DateRange = CDate(DateRange)
Exit Do
Else
MsgBox "YOU HAVE TO FILL THE DATE"
End If
Loop
With ActiveSheet
If Not .AutoFilterMode Then .Rows(1).AutoFilter
.Range("$A$1:$F" & Lastrow).AutoFilter Field:=1, _
Operator:=xlFilterValues, _
Criteria1:=Format(DateRange, "DD/MM/YYYY")
End With
End Sub
Generally speaking, addressing the ActiveSheet is dangerous unless the code controls which sheet is active at any given moment. In the above context it seems safer to name the sheet, like , With Worksheets("MySheet")