Hide row where Date less than this month
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: Hide row where Date less than this month
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Nov 2004
    Posts
    940
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Hide row where Date less than this month

    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

  2. #2
    Board Regular
    Join Date
    Mar 2013
    Posts
    697
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide row where Date less than this month

    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

  3. #3
    Board Regular
    Join Date
    Nov 2004
    Posts
    940
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide row where Date less than this month

    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.

  4. #4
    Board Regular
    Join Date
    Nov 2004
    Posts
    940
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide row where Date less than this month

    Oops 5 months

  5. #5
    Board Regular
    Join Date
    Mar 2013
    Posts
    697
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide row where Date less than this month

    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 ?

  6. #6
    Board Regular
    Join Date
    Nov 2004
    Posts
    940
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide row where Date less than this month

    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

  7. #7
    Board Regular
    Join Date
    Mar 2013
    Posts
    697
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide row where Date less than this month

    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

  8. #8
    Board Regular
    Join Date
    Nov 2004
    Posts
    940
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide row where Date less than this month

    Thanks for the time you spending on this.
    Unfortunately nothing because it is bringing in the date 07/01/2019

  9. #9
    Board Regular
    Join Date
    Nov 2004
    Posts
    940
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide row where Date less than this month

    Oops just realised that just day and month back to front

  10. #10
    Board Regular
    Join Date
    Mar 2013
    Posts
    697
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hide row where Date less than this month

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •