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

Dim Dt As Date
Dt = Date
With Columns("Q:T")
    .AutoFilter Field:=1, Criteria1:="Underwater Services"
    .AutoFilter Field:=4, Criteria1:=">=" & Val(Dt), Operator:=xlAnd
End With

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:

Option Explicit
Sub ModifiedReportGen()
'   This Sub Generates the Underwater, Topside, Pump Out and Mechanical _
'       Schedule Reports

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

'   Here we're formatting the reports for printing, setting column widths, _
'       moving columns to their appropriate locations for the reports, and _
'       establishing the Heading Columns.
    Columns("B:G").EntireColumn.Hidden = True
    Columns("H:H").Cut
    Columns("M:M").Insert Shift:=xlToRight
    Columns("P:P").Cut
    Columns("L:L").Insert Shift:=xlToRight
    Columns("N:O").EntireColumn.Hidden = True
    Columns("R:S").EntireColumn.Hidden = True
    Range("A1").Value = "Customer Name"
    Range("H1").Value = "Boat Name"
    Range("I1").Value = "Length / Model"
    Range("J1").Value = "Marina"
    Range("K1").Value = "Slip"
    Range("L1").Value = "Service Scheduled"
    Range("M1").Value = "Notes"
    Range("P1").Value = "Last Bottom Paint"
    Range("T1").Value = "Service Date"

'   This section hides any rows that aren't related to Underwater Services, _
'       as well as where the scheduled date is today or later.
    With Columns("Q:T")
        .AutoFilter Field:=1, Criteria1:="Underwater Services"
        .AutoFilter Field:=4, Criteria1:=">=" & Val(Dt), Operator:=xlAnd
    End With

'   This section sorts the report by Next Service Date (Column "T") and _
'       secondly by Marina (Column "J")
    Cells.Sort Key1:=Range("T2"), Order1:=xlAscending, Key2:=Range("J2") _
        , Order2:=xlAscending, Header:=xlYes

'   This section provides for the final formatting of the Underwater Services _
'       Schedule Report, by hiding Column "Q" (Job Type - it's handled in the _
'       heading of the report, formats the headings, and formats the body of _
'       the report
    Columns("Q:Q").EntireColumn.Hidden = True
    With Rows("1:1")
    .Font.Bold = True
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlBottom
    .WrapText = False
    End With
    With Cells
        .VerticalAlignment = xlTop
        .WrapText = True
    End With
    Columns("A:A").ColumnWidth = 18.14
    Columns("H:I").ColumnWidth = 12.14
    Columns("L:L").ColumnWidth = 14.16
    Columns("M:M").ColumnWidth = 20
    Columns("P:P").ColumnWidth = 11.14
    Columns("T:T").ColumnWidth = 11.14
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1" 'this could be in the next "with clause" & _
removed from here.
    End With

'   This section formats the report title area, sets page margins and tells _
'       printer how to print directionally
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .CenterHeader = _
        "&""Arial,Bold""Aquarius Yacht Services" & Chr(10) & "Underwater Services Schedule"
        .LeftFooter = "&D"
        .CenterFooter = "&P of &N"
        .LeftMargin = Application.InchesToPoints(0.75)
        .RightMargin = Application.InchesToPoints(0.75)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .Orientation = xlLandscape
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
    End With

'   And this next line actually sends the report to the printer
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub

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