autofilter of range problem

[tomleitch]

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.

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

 

[alansidman]

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]

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?

.AutoFilter Field:=39, Criteria1:="<=" & CLng(dat - 28)

 

[tomleitch]

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

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]

Peter, just seen your reply - and thanks for your help.

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


Thanks
Tom

 

[Peter_SSs]

Peter, just seen your reply - and thanks for your help.

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]

Err.... it seems to be working for me!

THanks again,
T