Filter on today's date forward

**Marcus Gee** · 10-21-2007, 07:16 AM

Greetings - I'm working on cleaning up the code generated by the macro recorder for printing some reports, and I need to tell the report to print all rows where Column Q contains the words "Underwater Services" and Row "T" contains a date >= today.

Here's the code I have presently at that section of the sub-routine:

HTML Code:

I've tried replacing the 10/22/2007 with date and today, but all I'm getting are reports with headers and no data. For what it's worth I don't really care about Columns "R" or "S" in the AutoFilter - I've got those columns hidden anyway.

If someone can offer suggestions that provide a cleaner way to filter on these two criteria and get the resuls described above I'd greatly appreciate it. Thanks in advance.

Marcus

**VBA Noob** · 10-21-2007, 07:27 AM

Maybe

Please Login or Register  to view this content.

VBA Noob

**Marcus Gee** · 10-21-2007, 07:53 AM

Thanks - I thought this one would be easy for you.

**VBA Noob** · 10-21-2007, 02:23 PM

Glad it helped

VBA Noob

**Marcus Gee** · 10-21-2007, 09:51 PM

I didn't have a chance to plug the print code in until this evening, but when I did it still wants to print everything (going back to the beginning of the year).

I've included the code I've got at this point below, along with copious comments. The code in question is the third section down, after the Dim Statement and report formatting. Any help would be appreciated. Thanks in advance - Marcus

HTML Code:

**Marcus Gee** · 10-22-2007, 12:13 AM

I think I might actually be getting this stuff.

I wrote a little MsgBox routine to confirm for myself that the correct value was getting put in the 'Dt' variable (of course it was, but it was a good exercise for me), and then I used:

HTML Code:

in place of:

HTML Code:

and the report ran as intended. Thanks again for helping me get untracked.

Marcus

**broro183** · 10-22-2007, 03:55 AM

Hi Marcus,

Good to hear that you're getting into this stuff & Good luck with dates - they can be a nuisance :-)

Your code can be streamlined a bit by removing all the ".select ... selection. ..." to something like:

Please Login or Register  to view this content.

I've changed the use of "columnwidth = 0" to "columns.hide = true" b/c that was what I'm used to (& I thought that it may be easier to unhide but when tested the use of either approach seems to make no difference).

Further suggestions for speeding up code are out there eg Dave McRitchie has links to the below:

http://www.avdf.com/apr98/art_ot003.html
http://www.cpearson.com/excel/optimize.htm
on his page:
http://www.mvps.org/dmcritchie/excel...htm#speedupVBA

hth
Rob

**VBA Noob** · 10-22-2007, 05:51 AM

Dates can be tricky in filter as this link shows. Look for

Range.AutoFilter

http://www.oaltd.co.uk/ExcelProgRef/...rogRefCh22.htm

VBA Noob

**Diesel9a1** · 11-13-2018, 07:36 AM

For some reason it doesn't show any up and coming dates after today.
I click a button on the sheet, it resets all filters, then it calls this routine:

Sub UpComing_Work()
'
' UpComing_Work Macro
' Up Coming shifts that are confirmed or proposed.
'

' This section defines the variable Dt as a Date, and stores the current _
' date in the variable Dt
Dim Dt As Date
Dt = Date

' Print the Date in Cell D1:
Range("D1").Value = Dt

' Show all entries after today's date:
ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=2, Criteria1:=">=" & Dt, Operator:=xlAnd

End Sub