# autofilter of range problem

#### tomleitch

##### Board Regular
Hi,

Wondering if anyone can help with this....

I'm trying to make my code set up two filters on my sheet - the first is a date on column 39 (based on pre defined date dat minus 28days) and the second is to filter for containing not blank cells.

Code:
``````With Sheets("OPS PLANNER")
.AutoFilterMode = False
With .Range("A9:AT5010")
.AutoFilter
.AutoFilter Field:=39, Criteria1:="<=" & dat - 28

.AutoFilter Field:=45, Criteria1:="<>"

End With

Exit Sub``````
It seems to run ok but for some reason is coming up with nothing even when there are results.

Probably something obvious! but can anyone help?

Thanks
Tom

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### alansidman

##### Well-known Member
Your second criteria translates to "doesn't equal". What are you trying to get it to do because "doesn't equal" doesn't equate.

#### Peter_SSs

##### MrExcel MVP, Moderator
Your second criteria translates to "doesn't equal". What are you trying to get it to do because "doesn't equal" doesn't equate.
Criteria1:="<>"
equates to "is not equal to blank", which is what the OP stated they want. If you record a macro to auto filter (Text Filter... -> Does not equal -> leave the top right box empty -> OK) the criteria is recorded as above.

Tom
If you change this line in your code does it help?
Rich (BB code):
``.AutoFilter Field:=39, Criteria1:="<=" & CLng(dat - 28)``

#### tomleitch

##### Board Regular
Morning, yep - that seemed to be what the problem was.

I have changed it to be 28 days older than today and it seems to work

Code:
``````dat = ">=" &  date - 28

With Sheets("OPS PLANNER")
.AutoFilterMode = False
With .Range("A9:AT5010")
.AutoFilter
.AutoFilter Field:=39, Criteria1:=dat

.AutoFilter Field:=45, Criteria1:="<>"

End With``````
Thanks
Tom

#### tomleitch

##### Board Regular

I think I've kind of done a similar thing to you suggest?

Thanks
Tom

#### Peter_SSs

##### MrExcel MVP, Moderator

I think I've kind of done a similar thing to you suggest?

Thanks
Tom
Your change doesn't work for me as I understand what you are trying to do, but if it is working for you, that's all that matters.

#### tomleitch

##### Board Regular
Err.... it seems to be working for me!

THanks again,
T