Filter column by Date Range produces no results

[rogerbowering]

I want the user of a worksheet to be able to select two dates and for the worksheet to display all rows where (column 9) falls between (or equal to those two dates)

I've recorded a macro and used code from another site and the problem I have is, when I run the code or the macro, the sheet status displays 0 of 10392 records found. The column that is being filtered shows the little filter icon. If I click on the filter icon for the column I see 'Text Filters' has been selected and 'Custom Filter'. When I click on 'Custom Filter' I can see the two dates I chose in the >= box and <= box and when I click on the OK button my sheet now displays the 1397 rows I expected to see

Why doesn't the VBA or running the macro complete the job and leave the filter 'unfinished' .

I'm using:
Dim StartDate As Date
Dim EndDate As Date
StartDate =InputBox("Enter Date From","Select Start Date")
EndDate=InputBox("Enter Date To","Select End Date")
ActiveSheet.Range("$A$1:$R$10393").AutoFilter Field:=9, Criteria1:=">=" & StartDate, Operator:=xlAnd, Criteria2:="<="&EndDate
Sheets("sheetname").Activate

I've noticed that the display I get when I run the macro shows my header/filter row and then rows 10394 onward

 

[rogerbowering]

PS As an aside it would be nice to have an Input box with both dates rather than two separate Input Boxes, but that's another thread

 

[dmt32]

Dates & Autofilter can be a bit of a problem.
Try passing your dates to long variable using dateserial something like this:

Sub DateFilter()
Dim StartDate As Variant
Dim EndDate As Variant
Dim lDateFrom As Long
Dim lDateTo As Long
Datefrom:
    StartDate = InputBox("Enter Date From", "Select Start Date")
    If StartDate = vbNullString Then Exit Sub
    If Not IsDate(StartDate) Then MsgBox "Not A Valid Date": GoTo Datefrom
DateTo:
    EndDate = InputBox("Enter Date To", "Select End Date")
    If EndDate = vbNullString Then Exit Sub
    If Not IsDate(EndDate) Then MsgBox "Not A Valid Date": GoTo DateTo
    
    lDateFrom = DateSerial(Year(StartDate), Month(StartDate), Day(StartDate))
    lDateTo = DateSerial(Year(EndDate), Month(EndDate), Day(EndDate))
    
    With ActiveSheet
    If .AutoFilterMode Then .AutoFilterMode = False
    .Range("$A$1:$R$10393").AutoFilter Field:=9, _
                                                  Criteria1:=">=" & lDateFrom, _
                                                  Operator:=xlAnd, _
                                                  Criteria2:="<=" & lDateTo
    End With
End Sub

Dave

 

[rogerbowering]

Brilliant, thank you


Dates & Autofilter can be a bit of a problem.
Try passing your dates to long variable using dateserial something like this:

Sub DateFilter()
Dim StartDate As Variant
Dim EndDate As Variant
Dim lDateFrom As Long
Dim lDateTo As Long
Datefrom:
    StartDate = InputBox("Enter Date From", "Select Start Date")
    If StartDate = vbNullString Then Exit Sub
    If Not IsDate(StartDate) Then MsgBox "Not A Valid Date": GoTo Datefrom
DateTo:
    EndDate = InputBox("Enter Date To", "Select End Date")
    If EndDate = vbNullString Then Exit Sub
    If Not IsDate(EndDate) Then MsgBox "Not A Valid Date": GoTo DateTo
    
    lDateFrom = DateSerial(Year(StartDate), Month(StartDate), Day(StartDate))
    lDateTo = DateSerial(Year(EndDate), Month(EndDate), Day(EndDate))
    
    With ActiveSheet
    If .AutoFilterMode Then .AutoFilterMode = False
    .Range("$A$1:$R$10393").AutoFilter Field:=9, _
                                                  Criteria1:=">=" & lDateFrom, _
                                                  Operator:=xlAnd, _
                                                  Criteria2:="<=" & lDateTo
    End With
End Sub

Dave[/QUOTE]