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.

 

[EXCEL MAX]

Hello K1600,
if you still not found cause of error "91"...
Check that your table have header with same name you are looking for with "With" statement.

 

[K1600]

Hello K1600,
if you still not found cause of error "91"...
Check that your table have header with same name you are looking for with "With" statement.

No, I've checked them over and over again but they are all correct. I have even checked the line which sets the worksheet and table but they are correct also.

It is creating the error all three of the 'with' statements.

 

[EXCEL MAX]

Are you sure that you testing this code from the post and that all objects from code related to the situation in the worksheet.
For example, be sure "Table8" name from code exist in the worksheet.

 

[MARK858]

What do you get with

.AutoFilter field:=ColMOTDate, Criteria1:=">=" & CLng(ColDate)

 

[K1600]

What do you get with

.AutoFilter field:=ColMOTDate, Criteria1:=">=" & CLng(ColDate)

If I run it with just that line in the 'with' (as below) then I get a run run-time error 1004 "AutoFilter method of Range class failed".

    With RTF
        .AutoFilter field:=ColMOTDate, Criteria1:=">=" & CLng(ColDate)
    End With

If I run it with the second line of code amended to include the CLng (as below) then I still get the run-time error 91 "Object variable or With block variable not set". In this form, it fails on the top line of code after the 'With'.

    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:=">=" & CLng(ColDate)
    End With

 

[K1600]

Are you sure that you testing this code from the post and that all objects from code related to the situation in the worksheet.
For example, be sure "Table8" name from code exist in the worksheet.

I'm not 100% sure what you mean but all of the coding relating to this is in Module1 and the table (Table8) is in the worksheet named "Pro" if that answers the question?

 

[MARK858]

then I still get the run-time error 91 "Object variable or With block variable not set

You normally get that error when it isn't finding the criteria in the Find. Can you check that the criteria is correct including the number of spaces (including any leading or trailing spaces in the cell). Also try changing LookIn:=xlFormulas to LookIn:=xlValues.

 

[K1600]

You normally get that error when it isn't finding the criteria in the Find. Can you check that the criteria is correct including the number of spaces (including any leading or trailing spaces in the cell). Also try changing LookIn:=xlFormulas to LookIn:=xlValues.

That was my first thought but I have checked, checked and checked again (even copy/pasted the result) of the column header but still it's not having it. Changing the LookIn to xlValues hasn't made a difference either

 

[MARK858]

What cell is "Last MOT date" in?