0

Could any help be offered to try and change the below macro to something more automated. The below macro will filter data in fonction of start and end dates entered manually. I wish to do away with having to enter the dates manually and put a code that allows to filter data 12 months ago. I can't use the inbuilt one year filter in excel because; supposing we're in the middle of the year and i filter for 12 months, it will indeed filter data for the whole of 2018 yet my request will have been to filter 12 months from last month before the actual month we may be in. If i've not been clear enough, i will be happy to be precise for anyone with any idea. Thank you in advance for any help

This is for excel 2010 vba. I tried to do some reseach but to no avail and any solution makes the code give an error. But am still searching for solutions

Sub filter_()
    Sheets("data1 ").Select

    MsgBox "12 months filter"

    lngStart = Application.InputBox("Enter start date of interest as dd/mm/yyyy", Type:=1 + 2)
    lngEnd = Application.InputBox("Enter end date of interest as dd/mm/yyyy", Type:=1 + 2)
    Sheets("RBT-RAT ").Select
    Range("Tableau1[[#Headers],[Date dernier freinage]]").Select
    ActiveSheet.ListObjects("Tableau1").Range.AutoFilter Field:=30, _
    Criteria1:=">=" & lngStart, _
    Operator:=xlAnd, _
    Criteria2:="<=" & lngEnd
End sub

I expect the code to be able to run without manually having to type in the start and end dates within which the filter should be applied and still filter in function of 12 months from which ever time of the year but without applying the filter from the current month. The actual macro posted just below does it but not automatically.

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Jemmy
  • 9
  • 5
  • You might benefit from reading [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and I recommend to activate `Option Explicit`: In the VBA editor go to *Tools* › *Options* › *[Require Variable Declaration](https://www.excel-easy.com/vba/examples/option-explicit.html)*. – Pᴇʜ Jan 23 '19 at 11:06

2 Answers2

0
ActiveSheet.ListObjects("Tableau1").Range.AutoFilter Field:=30, _
    Criteria1:=">=" & date-365, _
    Operator:=xlAnd, _
    Criteria2:="<=" & date

Or you can use dateadd("yyyy",-1,date) function to be more precise, change the period you wish to look back on

Nathan_Sav
  • 8,466
  • 2
  • 13
  • 20
  • `-365` doesn't work with lap years. Also this does not work for *"to filter 12 months from **last month before the actual month** we may be in"* – Pᴇʜ Jan 23 '19 at 10:48
  • @Pᴇʜ please see edit. My answer was to prompt the OP to research it, understand it, not just do it for them :o) – Nathan_Sav Jan 23 '19 at 10:53
  • Yes, but I think the OP wanted to have the result of *full* months (see the bold part of my comment) and not from/till the *current day of the month*. So I think that was the tricky part to solve: Finding the end of the previous month and *then* go 12 month backwards. – Pᴇʜ Jan 23 '19 at 10:59
  • 1
    :o) no worries, my first question here a long time ago, I was just prompted, felt a little bit deflated, but using those prompts helped me, so I try to do the same in my answers. There's always `cdate(application.WorksheetFunction.EoMonth(date,-1))` to :) – Nathan_Sav Jan 23 '19 at 11:03
  • Nathan thank, that actually is a good solution to me but not to my superior who doesn't want anything to do with programming so i have to keep looking for ways to auomate it to suit the desired usage... – Jemmy Jan 23 '19 at 12:21
  • @Jemmy sorry, I don't understand what input will be required after this??? – Nathan_Sav Jan 23 '19 at 12:23
  • Nathan, input as in data or what do you mean ? Hope i understood you, the macro is to fliter date info on a basis of 12 months in the past without including the current month and do it at any period of the year – Jemmy Jan 23 '19 at 12:27
  • I see, change >=date-365 to `>=dateadd("yyyy",-1,cdate(worksheetfunction.eomonth(date,-1)))` – Nathan_Sav Jan 23 '19 at 13:33
  • Hey Nathan, am receiving an error message on "yyyy" part of what you told me to change soi was wondering what was causing it – Jemmy Jan 23 '19 at 14:18
  • So you replace `lngStart` with `dateadd("yyyy",-1,cdate(worksheetfunction.eomonth(date,-1)))` you have a solution here from myself and Peh, so without seeing your screen, we cant see what you are doing. – Nathan_Sav Jan 23 '19 at 14:30
0

You can use the functions DateAdd and DateSerial.

Imagine today is 2019-01-23. Then …

DateSerial(Year(Date), Month(Date), 1)

… will give you the first day of the current month 2019-01-01. If we then subtract 1 day from that using …

LastDayOfPreviousMonth = DateAdd("d", -1, DateSerial(Year(Date), Month(Date), 1))

… we get the last day of the previous month 2018-12-31 which should be your end date. And if we subtract 12 month from that date …

StartDate = DateAdd("m", -12, LastDayOfPreviousMonth)

… you will get your start date 2017-12-31.

Sub filter_()

    MsgBox "12 months filter"

    Dim LastDayOfPreviousMonth As Date
    LastDayOfPreviousMonth = DateAdd("d", -1, DateSerial(Year(Date), Month(Date), 1))

    Dim StartDate As Date
    StartDate = DateAdd("m", -12, LastDayOfPreviousMonth)

    Sheets("RBT-RAT ").ListObjects("Tableau1").Range.AutoFilter Field:=30, _
        Criteria1:=">=" & StartDate, _
        Operator:=xlAnd, _
        Criteria2:="<=" & LastDayOfPreviousMonth
End Sub
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Peh thanks, i can't get the desired result. i edited as below: Dim StartDate As Variant Dim lngStart As Variant Dim astDayOfPreviousMonth As Variant Dim DateSerialll As Variant DateSerialll = DateSerial(Year(Date), Month(Date), 1) LastDayOfPreviousMonth = DateAdd("d", -1, DateSerial(Year(Date), Month(Date), 1)) StartDate = DateAdd("m", -12, DateOfLastMonth) Range("Tableau1[[#Headers],[Date dernier freinage]]").Select ActiveSheet.ListObjects("Tableau1").Range.AutoFilter Field:=30, _ Criteria1:=">=" & DateSerialll, _ Operator:=xlAnd, _ Criteria2:="<=" & LastDayOfPreviousMonth – Jemmy Jan 23 '19 at 12:15
  • PEH thank you so much as the code registers the instruction. When you go to personal filters you can see the desired filter (12 months) ago. However no infiormation is displayed on the sheet despite running the macro without any anomaly. I'm now trying to figure that mystery out now – Jemmy Jan 23 '19 at 13:47