Auto filter between a range of dates

K1600

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

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

EXCEL MAX

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

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

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

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

ADVERTISEMENT

What do you get with
VBA Code:
.AutoFilter field:=ColMOTDate, Criteria1:=">=" & CLng(ColDate)
 

K1600

Board Regular
Joined
Oct 20, 2017
Messages
159
What do you get with
VBA Code:
.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".

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

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

Board Regular
Joined
Oct 20, 2017
Messages
159

ADVERTISEMENT

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

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

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

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,872
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
What cell is "Last MOT date" in?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,964
Messages
5,599,069
Members
414,281
Latest member
Engjamal2021

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