VB: Filter based on today's date/date in column AND text in another

theshiggidy

New Member
Joined
Oct 13, 2016
Messages
6
Hi all,

I was using a macro attached to a button to filter rows based on the colour of a cell (determined by conditional formatting).

The code was:
-----------------
Sub Due()
'
' Due Macro
'

'
ActiveSheet.Range("$B$1:$Z$5997").AutoFilter Field:=10, Criteria1:=RGB(218, _
150, 148), Operator:=xlFilterCellColor
ActiveWindow.SmallScroll Down:=-6
End Sub
-----------------

However, this is now a shared workbook and for WHATEVER reason you can't use colour as a search filter in a shared workbook.

To layout how I think it can be done, but can't seem to find the right functions...

Column J is the date the report is due for distribution, and column K is the current status of the report (waiting, complete, etc).

Essentially I was thinking IF J is => TODAY () AND K = "Waiting"

I'm sure the solution is so simple to filter using the above criteria, any assistance would be excellent.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
Hello Theshiggidy,

Try the following code:-


Code:
Sub Test()

        Dim lr As Long
        Dim dDate As Date: dDate = [Today()]
        
Application.ScreenUpdating = False

        lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row   'You may have to change the "A" to "B"
        Sheet1.Range("B1:Z" & lr).AutoFilter 10, "=" & dDate
        Sheet1.Range("B1:Z" & lr).AutoFilter 11, "Waiting"

Application.ScreenUpdating = True

End Sub

The code just filters on the two criteria (Columns J & K). After filtering, what do you want to do?

I hope that this helps.

Cheerio,
vcoolio.

P.S. You may also have to change the sheet references.
 
Last edited:

theshiggidy

New Member
Joined
Oct 13, 2016
Messages
6
Thanks for the prompt response.

I don't want anything to happen as such after filtering, it's just an easy way to sort through all the entries to see what needs to be followed up.

Hmmm, it spits out a bug on
Code:
Sheet1.Range("B1:Z" & lr).AutoFilter 11, "Waiting"

I can see that dDate is equal to todays date, however I want the dates filtered to be today and older. Therefore showing any reports due today OR that are overdue.

Would that just be a matter of changing
Code:
Sheet1.Range("B1:Z" & lr).AutoFilter 10, "=" & dDate
to
Code:
Sheet1.Range("B1:Z" & lr).AutoFilter 10, ">=" & dDate

It's prbably of no assistance, but just in case my conditional formatting formula that was used previously to change the colur of the cell was: =AND(S2="",J2<=TODAY())

Yet again many thanks,

Shiggidy.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
Hello Theshiggidy,

I can see that dDate is equal to todays date, however I want the dates filtered to be today and older. Therefore showing any reports due today OR that are overdue.

The code as follows should do that for you:-

Code:
Sub Test2()

        Dim lr As Long
        
Application.ScreenUpdating = False

        lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
        Sheet1.Range("B1:Z" & lr).AutoFilter 10, "<=" & [Today()]
        Sheet1.Range("B1:Z" & lr).AutoFilter 11, "Waiting"

Application.ScreenUpdating = True

End Sub

What error are you receiving?

Cheerio,
vcoolio.
 

theshiggidy

New Member
Joined
Oct 13, 2016
Messages
6

ADVERTISEMENT

Hmmm,

Spitting out the same error: "Run-time error '1004' AutoFilter method of Range class failed

The debug highlights: Sheet1.Range("B1:Z" & lr).AutoFilter 11, "Waiting"
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
Does your data start in row 2 with headings in row 1? Are you using the correct sheet code references?

It would be best if you uploaded a sample of your workbook to a free file sharing site such as Drop Box and then post the link to your file back here. Be careful with any sensitive data.

Cheerio,
vcoolio.
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,079

ADVERTISEMENT

..................but in the meantime try:-


Code:
Sub Test2()
        
Application.ScreenUpdating = False

        Sheet1.UsedRange.AutoFilter 10, "<=" & [Today()]
        Sheet1.UsedRange.AutoFilter 11, "Waiting"

Application.ScreenUpdating = True

End Sub

or

Code:
Sub Test2()
        
Application.ScreenUpdating = False

        Sheet1.Cells(1).CurrentRegion.AutoFilter 10, "<=" & [Today()]
        Sheet1.Cells(1).CurrentRegion.AutoFilter 11, "Waiting"

Application.ScreenUpdating = True

End Sub

Cheerio,
vcoolio.
 

theshiggidy

New Member
Joined
Oct 13, 2016
Messages
6
Thanks vcoolio!!

All sorted, my silly mistake.

I wasn't using the correct sheet reference *applies palm to face*

Thank you again :)
 

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
That's excellent Shiggidy!

Don't worry. I do plenty of silly things too! Just ask the wife.

Glad that I was able to help.

BTW, hang on to the last two codes in post #7 also. Just a different slant on the procedure for future reference.

Cheerio,
vcoolio.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,361
Messages
5,641,660
Members
417,229
Latest member
BODYCOTE

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