Consulting

Results 1 to 6 of 6

Thread: AutoFilter Previous Months Data

  1. #1

    AutoFilter Previous Months Data

    I want to be able on a daily basis to filter out and delete data from the previous month.

    So when January comes round for example it uses January 2016 as the current month and deletes everthing prior to that date.

    I attach a sample spreadsheet and some code I've tried which literally filters out everything. The spreadsheet has a named range in cell "D1" called "Month".

    [CODE]Sub Macro1()
    '
    ' Macro1 Macro
    '
    '
    Dim Month As Range
    Range("D1").Select
    Set Month = Range("D1").CurrentRegion
    ActiveSheet.Range("$A$1:$B$2").AutoFilter Field:=2, Criteria1:= _
    "<""Month"", Operator:=xlAnd"
    End Sub[CODE]
    Attached Files Attached Files

  2. #2
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location
    You could try this one.
    Sub only_active_month_visible_plus_future()
    'startdate as date
    Dim startDate As Date
    'startdate as long for excel
    Dim lstartDate As Long
    
    
    'get start date, day = always 1
    startDate = DateSerial(Year(Now), Month(Now), 1)
    lstartDate = startDate
    'filter everything before current month
    'you see this month + future
    ActiveSheet.Range("B1").AutoFilter Field:=2, Criteria1:=">=" & lstartDate
    End Sub
    Charlize

  3. #3
    Charlize,

    That looks good from where I am sitting and I've just tried it and it works a treat. I did find some code on line that looked like it may have worked but it was far more convoluted than your method.

    So thankful to you.

    Thanks

  4. #4
    VBAX Regular
    Joined
    Nov 2019
    Posts
    16
    Location
    Hi @Charlize, can your code be modified to filter for two months prior including all the prior months ie in March filter for Jan and all months before, cheers.
    Sub only_active_month_visible_plus_future()'startdate as date
    Dim startDate As Date
    'startdate as long for excel
    Dim lstartDate As Long
    
    
    'get start date, day = always 1
    startDate = DateSerial(Year(Now), Month(Now), 1)
    lstartDate = startDate
    'filter everything before current month
    'you see this month + future
    ActiveSheet.Range("B1").AutoFilter Field:=2, Criteria1:=">=" & lstartDate End Sub

  5. #5
    VBAX Master
    Joined
    Jul 2006
    Location
    Belgium
    Posts
    1,286
    Location

    AutoFilter 2 months prior to current month

    This code will filter the rows where the date is more than 2 months prior to the current month of the date.
    In march, feb and jan is still shown. Older data is hidden. Play with numbers of month to be shown to test out.
    Sub show_two_months_prior_to_current_date_and_future()
        'startdate as date for date minus two months
        Dim startDate As Date
        'startdate as long for excel
        Dim lstartDate As Long
        
        'I use the worksheetfunction edate to substract 2 months from current month
        'now = the current day/month/year and time so we only need to get the date part
        'by using dateserial.
        'edate takes care when the century becomes one less.
        'In Jan 2021 we need also Dec 2020 and Nov 2020 to be included in the filter
        'Change -2 to -3 or more to get dates prior of 2021.
        startDate = Application.WorksheetFunction.EDate(DateSerial(Year(Now), Month(Now), 1), -2)
        'startDate = DateSerial(Year(Now), Month(Now), 1)
        lstartDate = startDate
        'filter everything from two months prior to current date
        'you see rows starting from two months prior to current month of the date
        ActiveSheet.Range("B1").AutoFilter Field:=2, Criteria1:=">=" & lstartDate
    End Sub
    Charlize

  6. #6
    VBAX Regular
    Joined
    Nov 2019
    Posts
    16
    Location
    Thank you very much Charlize

Posting Permissions

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