Auto filter between a range of dates

[K1600]

I am trying to apply a filter to a data set which is with an excel formatted table (Table8) but the first bit I need to do is only show data where the date in the 'Date' column is less than the date in the 'Last MOT date' column.

I currently have the following coding but I keep getting a run-time 91 error on the first line of the With statement,

Dim RTF As Range
Dim ColDate As Long
Dim ColMOTDate As Long
Dim ColRemoval As Long
Dim ColComplete As Long

Set RTF = Worksheets("Pro").Range("Table8").Range("A1").CurrentRegion

'Filters date
    With RTF
        ColMOTDate = .Rows(1).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column - .Column + 1
        .AutoFilter field:=ColMOTDate, Criteria1:=">=" & ColDate
    End With
'Filters 'Removal' column
    With RTF
        ColRemoval = .Rows(1).Find(what:="Removal means", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column - .Column + 1
        .AutoFilter field:=ColRemoval, Criteria1:="Inspection"
    End With
'Filters Competle column
    With RTF
        ColComplete = .Rows(1).Find(what:="Complete?", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column - .Column + 1
        .AutoFilter field:=ColComplete, Criteria1:="No"
    End With

I've also tried this but I get a run-time 1004 error:

Set RTF = Worksheets("Pro").Range("Table8").Range("A1").CurrentRegion

'Filters date
    With RTF
        .AutoFilter field:=ColMOTDate, Criteria1:=">=" & ColDate
    End With

I would be grateful of any assistance.

 

[MARK858]

So you are filtering based on the API date? if yes what cell is the date in (including the sheet name if on another sheet).

What I am asking to be clear is where is the date that you want it to be greater than.

 

[K1600]

So you are filtering based on the API date? if yes what cell is the date in (including the sheet name if on another sheet).

The filter is to pull results for the rows where the API date (Last MOT date) is >= to the date in the "Date" column.

Both dates are in the same sheet "Pro", the "Date" is column A and the "Last MOT date" which is from the API is column Y.

 

[MARK858]

So where in Column A is the date that we are using to filter column Y?

 

[K1600]

So where in Column A is the date that we are using to filter column Y?

It's in the same table (Table8) so A5.

 

[MARK858]

Dim ColRemoval As Long
Dim ColComplete As Long

Set RTF = Worksheets("Pro").Range("Table8").Range("A1").CurrentRegion

ColDate = CLng(Worksheets("Pro").Range("A4"))

'Filters date
    With RTF

 

[K1600]

Dim ColRemoval As Long
Dim ColComplete As Long

Set RTF = Worksheets("Pro").Range("Table8").Range("A1").CurrentRegion

ColDate = CLng(Worksheets("Pro").Range("A4"))

'Filters date
    With RTF

I think I might not have been quite quick enough in editing my reply from A4 to A5 and as such assume that the A4 in your line should be A5? If it should be A4 then I get a type mismatch error and if it should be A5 then I am still getting the run-time 1004 on the .AutoFilter Field:=colMOTDate, Criteria1:=">=" & CLng(colDate) line.

 

[MARK858]

Then I am afraid that I need to see your workbook. Can you upload it to a free file hosting site like www.box.com or www.dropbox.com, mark it for sharing and paste the link it provides in the thread.

Make sure that you also sanitize any sensitive data and format any dates as a number before uploading the file.

 

[K1600]

Then I am afraid that I need to see your workbook. Can you upload it to a free file hosting site like www.box.com or www.dropbox.com, mark it for sharing and paste the link it provides in the thread.

Make sure that you also sanitize any sensitive data and format any dates as a number before uploading the file.

Thanks for that. I will have to sanitize quite a lot of both the coding and the data as there are various things in both which are sensitive however I will get onto it. I will try and have another got at it tomorrow and it I get to the point of throwing it out of the window I will set about sanitizing it and come back to you. Thanks for all your help so far.