Hide row where Date less than this month

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
1,001
Office Version
  1. 365
Platform
  1. Windows
I have dates in column C from row 8 and would like to have a macro which on accessing the sheet would hide all rows where the month and year of the date in column C is less than the now.

So as at today I would only want to see rows from July onwards.

As ever my thanks for any assistance
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
Perhaps try filtering the dates
Code:
Private Sub Worksheet_Activate()
With Range("C7", Range("C" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, _
                Criteria1:=">=" & DateSerial(Year(Now), Month(Now), 1), _
                Operator:=xlFilterValues
End With
End Sub
 
Upvote 0
Hi NoSparks and many thanks for your suggestion.
Just to clarify c7 is the header row so data currently exists from c8 to c238

Using this filter for some reason it filters up to 22/1/19 leaving about 6 months rows that are not required.

Looking in the list range of the advanced filter settings it is showing c7:c238 which is correct.
 
Upvote 0
The criteria for the filter is greater than or equal to the first of the current month of the current year,
which right now Excel stores as 43662 (the number of days since January 1, 1900) and
22/1/19 would be stored as 43487 which would be hidden by the filter.

If you select that 22/1/19 cell and format it as number, do you get 43487 ?

Can you record a macro of manually filtering to the desired results ?
 
Upvote 0
Apologies for delay in getting back.

I have started working on a smaller data set as below:

Date
Tue 18/06/19
Tue 25/06/19
Fri 28/06/19
Mon 01/07/19
Tue 02/07/19
Tue 09/07/19
Tue 16/07/19

I have recorded the following using a filter of everything after and including 1st July 2019 and it works fine showing only those records in July

Selection.AutoFilter
ActiveSheet.Range("$C$7:$E$14").AutoFilter Field:=1, Criteria1:= _
">=01/07/2019", Operator:=xlAnd

On the same set of data using your code it shows everything as it is looking for a date of 12/01/1905

So the issue seems to be picking up todays month

Oh and I can confirm that all of the dates are true numbers:

43634 18/06/2019
43641 25/06/2019
43644 28/06/2019
43647 01/07/2019
43648 02/07/2019
43655 09/07/2019
43662 16/07/2019
 
Upvote 0
Okay, let's build the criteria a little differently and see if that works
Code:
Private Sub Worksheet_Activate()
With Range("C7", Range("C" & Rows.Count).End(xlUp))
    .AutoFilter Field:=1, _
                Criteria1:=">=" & "01/" & Format(Month(Now), "00") & "/" & Format(Year(Now), "0000"), _
                Operator:=xlFilterValues
End With
End Sub
 
Upvote 0
Thanks for the time you spending on this.
Unfortunately nothing because it is bringing in the date 07/01/2019
 
Upvote 0
Oops just realised that just day and month back to front
 
Upvote 0
No point me playing around with this as it works on my computer (my system regional settings use m/d/y)
I don't know what to change to work on yours, I thought just formatting to match your recorded macro but apparently not.
Anyway, this isn't an unusual requirement so somebody else will be able to assist for sure.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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