Filtering by InputBox date entries and using Autofilter with 3 criteria

[Viktor]

Hi everyone,

I could use some help with a macro which filters a column of dates based on two dates that the user enters OR if the cell is empty.

This is what I have so far:



Sub CPT_Macro()


Dim lngStart As Long, lngEnd As Long
lngStart = InputBox("Enter stard date of interest")
lngEnd = InputBox("Enter end date of interest")
Range("AH7:AH8000").AutoFilter field:=1, _
Criteria1:=">=" & lngStart, _
Operator:=xlAnd, _
Criteria2:="<=" & lngEnd
Operator:=xlOr, _
Criteria3:=""

End Sub


There seem to be two problems:
1. I get a Type mismatch error as soon as I enter the start date (using dd.mm.yyyy, if that matters)
2. The two lines starting with "Operator:=xlOr, _" are red (maybe I cant autofilter with xlOr?)

I hope someone smarter can figure this out - thanks a lot!
Viktor

[mancubus]

hi.

1. yes. try:

Application.InputBox(prompt:="Enter stard date of interest", Type:=1)

2. delete Operator:=xlOr, Criteria3:="" bit.

[Viktor]

1. Thanks mancubus, that works!

2. What could I do to include the blank cells as well?

[mancubus]

you're welcome.

record a macro while you are manually filtering with your criteria and post the recorded macro here.