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
14,005
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Ok try changing
VBA Code:
Dim ColMOTDate As Long
to
VBA Code:
Dim ColMOTDate As Range
and
VBA Code:
ColMOTDate = .Rows(1).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column - .Column + 1
to
VBA Code:
Set ColMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)
and use
VBA Code:
CLng(ColDate)

You still might need to change xlFormulas to xlValues

Btw if you want to refer to a the headers in a Table it is easier to use
VBA Code:
Worksheets("Pro").ListObjects("Table8").HeaderRowRange
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
Ok try changing
VBA Code:
Dim ColMOTDate As Long
to
VBA Code:
Dim ColMOTDate As Range
and
VBA Code:
ColMOTDate = .Rows(1).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext).Column - .Column + 1
to
VBA Code:
Set ColMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)
and use
VBA Code:
CLng(ColDate)

You still might need to change xlFormulas to xlValues

Btw if you want to refer to a the headers in a Table it is easier to use
VBA Code:
Worksheets("Pro").ListObjects("Table8").HeaderRowRange
That's gone back to runtime error 91 and debug takes me to the colMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext) line again.

Just in case I have missed something, this is what I've got from the start now:

VBA Code:
Dim RTF As Range
Dim colDate As Long
Dim colMOTDate As Range

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

'Filters date
    With RTF
        colMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)
        .AutoFilter Field:=colMOTDate, Criteria1:=">=" & CLng(colDate)
    End With

Sorry for being a pain...
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,005
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
You are missing the Set

Rich (BB code):
  Set colMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
You are missing the Set

Rich (BB code):
  Set colMOTDate = .Rows(4).Find(what:="Last MOT date", LookIn:=xlFormulas, lookat:=xlWhole, searchorder:=xlByColumns, searchdirection:=xlNext)
Oops, sorry I missed that.

That steps through that now but errors again with a runtime 1004 "AutoFilter method of range class failed" at .AutoFilter Field:=colMOTDate, Criteria1:=">=" & CLng(colDate)
 

MARK858

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

ADVERTISEMENT

Hover your mouse over colDate and colMOTDate and tell me what Value they show and also in a spare cell put the formula =ISNUMBER(Y7) changing the Y7 to a cell that has a date and tell me if it says FALSE or TRUE.
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
Hover your mouse over colDate and colMOTDate and tell me what Value they show and also in a spare cell put the formula =ISNUMBER(Y7) changing the Y7 to a cell that has a date and tell me if it says FALSE or TRUE.
Hovering over them in this bit..... .AutoFilter Field:=colMOTDate, Criteria1:=">=" & CLng(colDate) gives me:

colMOTDate = "Last MOT date"
colDate = 0

The isnumber formula returns a 'TRUE' value.
 

MARK858

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

ADVERTISEMENT

Ok that is quite obvious, you aren't assigning a date to colDate anywhere in your code.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,005
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What date are you filtering for? and are you hard coding it or is it a date in a cell?
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
166
What date are you filtering for? and are you hard coding it or is it a date in a cell?
Both columns will already have a date in them. The 'Date' column is a manual input and the 'Last MOT date' one is obtained from an API but at the point of this filter being applied both are in the spreadsheet as an entry.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,935
Messages
5,627,705
Members
416,268
Latest member
zRGZgDNnmaPhepwviEou

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