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

[theshiggidy]

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.

 

[vcoolio]

Hello Theshiggidy,

Try the following 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.

 

[theshiggidy]

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

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

Sheet1.Range("B1:Z" & lr).AutoFilter 10, "=" & dDate

to

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]

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

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]

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]

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]

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

Sub Test2()
        
Application.ScreenUpdating = False

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

Application.ScreenUpdating = True

End Sub

or

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]

Thanks vcoolio!!

All sorted, my silly mistake.

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

Thank you again

 

[vcoolio]

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.