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.
 

MARK858

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

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

K1600

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,974
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
So where in Column A is the date that we are using to filter column Y?
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,974
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Rich (BB code):
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

Board Regular
Joined
Oct 20, 2017
Messages
166
Rich (BB code):
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

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

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

Watch MrExcel Video

Forum statistics

Threads
1,127,270
Messages
5,623,737
Members
415,986
Latest member
C_Braga

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