AutoFilter to Today's Date

[frank_AL]

I am trying to help provide a solution to Forum Post titled: "If column cells certain values, read name and vlookup in another sheet for email"
I have worked back and forth with the requestor and had a solution that met his initial criteria.
In his last request he is wanting to Filter the data to only rows with Today's Date in Column E. Column E is formatted as Short Date.
I have now spent a couple hours Googling for a solution and have tried many that are posted on MrExcel and other Excel Forums but have been unable to find a workable solution.

Any ideas would be much appreciated.

 

[RoryA]

Assuming they are actual date values, not text, I'd suggest something along these lines:

Range("A1").Currentregion.autofilter field:=5, criteria1:=">=" & clng(date), operator:=xlAnd, criteria2:="<=" & clng(date)

 

[frank_AL]

Rory, thanks for the quick response. I’ll give it a try.

 

[frank_AL]

Rory,

Your code worked, thanks for your help.

I'm fairly new to code development in VBA. I used Excel extensively throughout my 40 year professional career. I took up programming in retirement and it helps keep me busy.

I am amazed at the awkwardness of some of the coding methods as a result of what Microsoft delivered. Obviously the MrExcel Forum has been around a long time. Is there ever any communication back to Microsoft from the development community in an effort to correct or improve the issues that require such awkward gyrations to achieve a desired result? I've listed just a couple below that I've encountered in the past couple of years. I expect someone with your experience would have a much longer list!

1. Cell references in Excel have always been Column then Row, "A1". The Range command follows this syntax but then along comes Cells and now it uses Row then Column. Certainly a very handy function compared to the need to convert a column number to a character but why not stay with the conventional Column then Row?

2. Autofilter on Date = Today. I spent close to 2 hours trying to get this to work before I created this Thread. There are many supposed solutions posted on this and other forums. It simply shouldn't be this hard.

3. The double negative: "If Not Intersect(Target, Range("BN:BN")) Is Nothing Then". Really? They couldn't just provide a solution here that is straight forward without requiring a double negative.

Anyway, that is my rant for the day. Thanks again for you assistance and your willingness to share your knowledge with others.

Frank_al

 

[RoryA]

You're welcome.

Re #3 , you can simply alter your If structure so that you don't need the Not.

In general terms, other than show-stopping bugs, there is no point at all in telling Microsoft about things you don't like in VBA. They don't see it as the future, end of story. (which is not to say that it will go away any time in the foreseeable future, but they have no interest in developing it)