Solved auto filter using a date

September 7, 2016 at 16:11:20
Specs: Windows 7
I need to add an autofilter for expiration dates in the next 7 days:

I have the basic part but don't know how to program the criteria to check if date expires in the next 7 days. The cell it is evaluating is date format and sometimes the date is blank so I don't know if I need to take that into account:

ActiveSheet.Range("$A$6:$ET$1000").AutoFilter Field:=19, Criteria1:=???????

See More: auto filter using a date

Reply ↓  Report •

September 8, 2016 at 06:21:29
✔ Best Answer
Try something like this. I doubt this precisely correct for your requirements but the concept should be valid.

Criteria1:="<" & Now + 7

The concept is that you are using the "<" as a text string as the first part of the Criteria1 argument and the VBA Now function to pass the current date (plus 7 days) as the actual criteria. The quotes tell VBA that what is contained within is a text string, so if you included the Now + 7 within the quotes it would not be evaluated.

You may need a more complex string as the actual argument but the basic concept is that text must be enclosed in quotes and anything that you want VBA to evaluate must be outside the quotes. Obviously, it all gets concatenated together with the &.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Reply ↓  Report •

September 8, 2016 at 11:48:06
You have been a life saver for me, thank you very much for your great advice and tutelage :-)

Reply ↓  Report •

Related Solutions

Ask Question