Hiding Future Rows Containing Future Dates

Raj_121

New Member
Joined
Mar 27, 2013
Messages
3
Hi, I was hoping someone could help me.

My Excel Spreadsheet is updated each month with that months' figures. imported from elsewhere


I.E.

In Cell Range D3-D75 Contains Dates (Jan 2010 - Dec 2015)
Thus the Rows 3 - 75 have corresponding data for each month. It is updated with the next months' figures on a monthly basis.

I want to create a Macro where I can UNHIDE the relevant Rows (all dates from Previous Months to the Present Month) and HIDE future months that havent been filled.

I have created a drop-down list containing each month correspding to the Cells D3-D75 in A2.
I was thinking of creating a loop in order for Cell A2 to lookup the Array Cells(D3-D75) until the dates correspond with eachother and thus hide the following future date rows

Kind of like a filtering system but instead of filtering make it so that the filter works on a cumulative basis i.e. showing everything up to that point and hide cells beyond that point.

Please Someone Help Me. I'm new to VBA and im just trying to get to grips with coding.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
From the sounds of it, you just need:
Code:
Sub filterDates()
   Range("D2:D75").AutoFilter field:=1, Criteria1:="<=" & CLng(Date)
End Sub
 
Upvote 0
That is great Rory Thank You.

One Quick Question, Is there any way I could
a)
change the code so that is the last month it shows is the previous month...... would i need to change CLng(Date)?

b)
is there any way I could change the code so that I could maybe have a Drop-Down list in Cell A1, and use that as the filter reference so that it will show all relevant rows up to the point where A1 is equal to the last row i want to keep.


Thank You so much.
 
Upvote 0
a) change CLng(Date) to CLng(Date - Day(Date))

b) Yes - use Range("A1").Value2 as the criterion instead of CLng(Date) assuming that A1 contains an actual date value.
 
Upvote 0
Sorry for being a pest, but I have just found that a filter function will not work, because below the monthly figures, the data is categorized Quarterly and then Yearly.

I was thinking of doing an Condition, using the if function. Such that, if the value in A1 (E.g.- Mar-2013) Corresponds to row number in Column D From Range (D3-D75)
It will Unhide everything up to that point and hide the rest of the range (D3-D75)

Maybe using a loop function to check if each row in Column D Range (D3 - D75) is equal to the cell value A2 and then stop.
 
Upvote 0
Sorry, I don't follow what the problem is.
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

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
Back
Top