Auto filter between a range of dates

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
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,

VBA Code:
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:

Code:
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.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,978
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
So you are saying "Last MOT date" is in Y4? Yes?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,978
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What does
VBA Code:
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).
VBA Code:
Sub testit3()
    Debug.Print "| &"; Worksheets("Pro").Cells(4, "Y").Value; "& |", Len(Worksheets("Pro").Cells(4, "Y").Value)
End Sub
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166

ADVERTISEMENT

What does
VBA Code:
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).
VBA Code:
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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,978
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
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

Board Regular
Joined
Oct 20, 2017
Messages
166

ADVERTISEMENT

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,978
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What are you trying to do with the
VBA Code:
.Column - .Column + 1
part? do you not want it to filter on column Y?
 

EXCEL MAX

Active Member
Joined
Nov 11, 2020
Messages
384
Office Version
  1. 2007
Platform
  1. Windows
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

Board Regular
Joined
Oct 20, 2017
Messages
166
What are you trying to do with the
VBA Code:
.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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,602
Messages
5,625,750
Members
416,133
Latest member
ToseSenpai

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top