Hide row where Date less than this month

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
976
Office Version
365
Platform
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
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
890
Office Version
2010
Platform
Windows
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
 

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
976
Office Version
365
Platform
Windows
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.
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
890
Office Version
2010
Platform
Windows
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 ?
 

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
976
Office Version
365
Platform
Windows
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
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
890
Office Version
2010
Platform
Windows
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
 

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
976
Office Version
365
Platform
Windows
Thanks for the time you spending on this.
Unfortunately nothing because it is bringing in the date 07/01/2019
 

lapta301

Well-known Member
Joined
Nov 12, 2004
Messages
976
Office Version
365
Platform
Windows
Oops just realised that just day and month back to front
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
890
Office Version
2010
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,751
Messages
5,482,645
Members
407,356
Latest member
fabioargenton

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top