Excel 2007 Autofilter

[jbeaucaire]

I don't have Excel 2007, the macro written below on my Excel 2003 functions perfectly, but on my colleagues computer, the 3rd filter actually hides ALL rows leaving nothing visible, he's using Excel 2007 and Excel 2010.

The filters in blue work the same for both of us, first filtering to show only the customer name rows, then showing only the rows that are blank in the "Paid" column 30.

I've highlighted in red the part that is problematic, filtering a column of dates by a begin and end date range.

Sub GenerateStatement()
Dim fDate As Date, lDate As Date
Dim Cust As String, fName As String
Dim wbRECORDS As Workbook, wsSTATEMENT As Worksheet
Dim MyArr As Variant, V As Long, LR As Long

    If Not IsDate(Me.Range("H7")) Or Not IsDate(Me.Range("H10")) Then
        MsgBox "Please enter correct START and END dates."
        Exit Sub
    ElseIf Me.Range("H4") = "" Then
        MsgBox "Please select a Company."
        Exit Sub
    End If
    
    fDate = Me.Range("H7")
    lDate = Me.Range("H10")
    Cust = Me.Range("H4").Value
    
    Application.ScreenUpdating = False
    Set wbRECORDS = Workbooks.Open(ThisWorkbook.Path & "\NVM record form.xlsx")
    
    With wbRECORDS.Sheets("Sheet1")
        .AutoFilterMode = False     'reset any prior filters
        .Rows(1).AutoFilter         'turn on new autofilter
        .Rows(1).AutoFilter Field:=2, Criteria1:=Cust
        .Rows(1).AutoFilter Field:=30, Criteria1:="="
        .Rows(1).AutoFilter Field:=29, Criteria1:=">=" & fDate, _
                      Operator:=xlAnd, Criteria2:="<=" & lDate
        LR = .Range("A" & .Rows.Count).End(xlUp).Row
        If LR = 1 Then
            MsgBox "No data for that period"
        Else
            fName = ThisWorkbook.Path & "\Statements\" & Cust & Format(fDate, " MMMM-YYYY") & ".xlsx"
            If Len(Dir(fName)) > 0 Then
                If MsgBox("Statement already detected.  Create a new one to replace the old?", _
                    vbYesNo + vbCritical, "Replace Existing Statement?") = vbNo Then GoTo ErrorExit
            End If
            'open template
            Workbooks.Add Template:=ThisWorkbook.Path & "\NVM.Statement.xlt"
            ActiveSheet.Name = Format(fDate, "MMM-YYYY")
            Set wsSTATEMENT = ActiveWorkbook.Sheets(1)
            wsSTATEMENT.Range("C3").Value = "STATEMENT - " & Format(fDate, "MMMM YYYY")
            
            .Range("S2:S" & LR).Copy
                wsSTATEMENT.Range("A16").PasteSpecial xlPasteValues
            .Range("U2:U" & LR).Copy
                wsSTATEMENT.Range("B16").PasteSpecial xlPasteValues
            .Range("AC2:AC" & LR).Copy
                wsSTATEMENT.Range("C16").PasteSpecial xlPasteValues
            .Range("C2:C" & LR).Copy
                wsSTATEMENT.Range("D16").PasteSpecial xlPasteValues
            .Range("X2:X" & LR).Copy
                wsSTATEMENT.Range("E16").PasteSpecial xlPasteValues
            .Range("Z2:AB" & LR).Copy
                wsSTATEMENT.Range("F16").PasteSpecial xlPasteValues
                
            wsSTATEMENT.Range("A16:A400").SpecialCells(xlBlanks).EntireRow.Delete xlShiftUp
            
            MyArr = Split(.Range("R" & .Rows.Count).End(xlUp), ",")
            For V = LBound(MyArr) To UBound(MyArr)
                wsSTATEMENT.Range("A6").Offset(V).Value = Trim(MyArr(V))
            Next V

            Application.DisplayAlerts = False
            ActiveWorkbook.SaveAs fName
            ActiveWorkbook.Close False
            MsgBox "Statement generated"
        End If
ErrorExit:
        .AutoFilterMode = False     'reset filters, this line may be unnecessary
    End With

    wbRECORDS.Close False
    Application.ScreenUpdating = True
End Sub

The format of the dates in that column are DD-MMM-YY. But I've altered the red code to include formatting and it still works for me. For him, he keeps getting the "No data for that period" message.

Any tips would be appreciated.

 

[jbeaucaire]

Although it works without it on my system, I retried adding the formatting to that code and it worked for him. So this seems to be required for his success:

        .Rows(1).AutoFilter Field:=29, Criteria1:=">=" & Format(fDate, "D-MMM-YY"), _
                      Operator:=xlAnd, Criteria2:="<=" & Format(lDate, "D-MMM-YY")

 

[Marcelo Branco]

Hi,

I think (made some tests with dd/mm/yyyy dates) that this works too

.Rows(1).AutoFilter Field:=29, Criteria1:=">=" & CLng(fDate), _
Operator:=xlAnd, Criteria2:="<=" & CLng(lDate)

M.