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.

 

[K1600]

What cell is "Last MOT date" in?

It's column Y with row 4 being the headers and row 5 being my first row of data.

 

[MARK858]

So you are saying "Last MOT date" is in Y4? Yes?

 

[K1600]

So you are saying "Last MOT date" is in Y4? Yes?

Yes.

 

[MARK858]

What does

Sub MessageMe()
Msgbox Worksheets("Pro").Range("Table8").Range("A1").CurrentRegion.Rows(1).Row
End Sub

give you?

and the code below will print something in the Immediate window. Can you Copy it and Paste the result in the thread (do not type it in the thread, I need it copy/pasted).

Sub testit3()
    Debug.Print "| &"; Worksheets("Pro").Cells(4, "Y").Value; "& |", Len(Worksheets("Pro").Cells(4, "Y").Value)
End Sub

 

[K1600]

What does

Sub MessageMe()
Msgbox Worksheets("Pro").Range("Table8").Range("A1").CurrentRegion.Rows(1).Row
End Sub

give you?

and the code below will print something in the Immediate window. Can you Copy it and Paste the result in the thread (do not type it in the thread, I need it copy/pasted).

Sub testit3()
    Debug.Print "| &"; Worksheets("Pro").Cells(4, "Y").Value; "& |", Len(Worksheets("Pro").Cells(4, "Y").Value)
End Sub

The first one the MsgBox returns a "1" result and the response in the intermediate window is:

| &Last MOT date& | 13

 

[MARK858]

The first one the MsgBox returns a "1" result

Then your code is looking in row 1 and not row 4 and so won't find "Last MOT date" in Y4.

 

[K1600]

Then your code is looking in row 1 and not row 4 and so won't find "Last MOT date" in Y4.

Ah OK, thanks for that, that makes sense now. I'm relatively new to all this and I had used a previous code to try to filter..

I have just amended my .Rows(1) to .Rows(4) and it now steps past that but it is still stopping me at .AutoFilter Field:=colMOTDate, Criteria1:=">=" & (colDate) with the run-time error 1004 "AutoFilter method of range class failed".

 

[MARK858]

What are you trying to do with the

.Column - .Column + 1

part? do you not want it to filter on column Y?

 

[EXCEL MAX]

If you have more then one table in the worksheet , may be "Table8" is name of some other table.
To check that, click cell "Y4" and in the main menu Design tab see the real name of the table.

 

[K1600]

What are you trying to do with the

.Column - .Column + 1

part? do you not want it to filter on column Y?

I basically want it to give me the rows where the date in the "Last MOT date" column (column Y) is greater than the date in the "Date" column (column A). There will then also be two further filters but these are just relevant to single columns so I should just be able to add them once I have sussed this bit.

If I'm honest I'm not sure what the .Column - .Column + 1 part does. I wasn't sure how to do the autofilter so took the code from a previous project where it worked.