Hiding and Unhiding rows based on their dates VBA

[jecowher1]

Hi everyone! New to VBA and trying to teach myself (unsuccessfully). I want to hide individual rows in a range from 6-36 based on dates in those rows being greater than or equal to a reference cell that changes daily based on a formula (today()-1). i have attached an example of my worksheet to show what i am working with and showing what i would like it to do. Just don't know how to get there using code.

Any help is much appreciated!

Book..xlsx

[Bernie Deitrick]

One line of code should do it - always try to use native Excel functionality when you can, and record macros to then modify to what you need

Please Login or Register  to view this content.

Note that "< today" is the same as "<=today -1"

[Alf]

Perhaps you could use the active line in Bernie's sub in a "Private Sub Workbook_Open()", this way the macro will run whenever you opens the workbbok.

Ooooooooops my bad! In the Visuasl Basic Window right click on "ThisWorkbook", then click on "View Code" and paste this code in the new windows that opends.

Please Login or Register  to view this content.

Alf

[jecowher1]

That works great! The only thing is when I apply it to my actual data it puts the filters at the top of the page (row 1) and hides my headers.. but when I applied it to my example sheet it put the filters on row 5 and kept the headers.. any idea as to why this is? I'm sure i can find a work around if there is no other way.

Again thank you for the help!!

[Bernie Deitrick]

You need to have an entirely blank row 4 - any contiguously filled cells will allow the current region method to select up to row 1. If you don't want to empty out row 4, insert a new row 5 and change the code to

ActiveSheet.Range("$A$6").CurrentRegion.AutoFilter Field:=6, Criteria1:="<" & Date

or use

ActiveSheet.Range("$A$1").CurrentRegion.Offset(4).AutoFilter Field:=6, Criteria1:="<" & Date

but then make sure that you do have a contiguous path from A1 to the data set.