0

I have a datasheet that was given to me and I was asked to create filters to display specific data while simultaneously filtering dates. He would like to be able to make these changes with option buttons. I have no experience with Excel or macros, and despite being a technical person I've basically gotten nowhere.

The table stretches from columns A to AB and rows 1 to 10,000. Rows 1 through 12 make up the header. Column K is labeled Project Status and contains the words, either Open or Closed. I need to be able to turn this filter on and off, displaying all projects or just the Open projects, with two option buttons grouped together. I then need to be able to specify a date range that will work whether displaying either all or only open projects, essentially filtering the data even further. (ex. all Open projects in the last 6 months) The date range must be specified as follows

less than 3 months old

less than 6 months old

less than 1 year old

between 1 and 3 years old

between 3 and 5 years old

greater than 5 years old

7 more buttons would be placed in a separate group box to select those date ranges and the seventh button to display all the dates. This filter must also be calculated from the current date. I've had the most trouble with this as I'm not sure how to create a filter for the past five years, that will still be accurate in the years to come, without manually changing the date it's based on.

Any help with this would be greatly appreciated.

0

Since what you are doing is basically the same thing repeated 7 times, here is how to do it once. I set up some data in 3 columns. The data is obviously different from yours but the same idea will apply.

Initial setup on Worksheet

Here are my 3 columns with some data populated in them.

enter image description here

VBA coding

Press Alt + F11 to open the VBA side. On the top select Insert > Module and then put something like this there:

Public Sub Filter1()
    Range("A1:C1").AutoFilter
    Range("$A$1:$C$6").AutoFilter Field:=1, Criteria1:="4"
End Sub

Explanation of code used

The part of the code:

Range("A1:C1").AutoFilter

creates the initial filter (where you see the drop down boxes appear)

This part of course does the actual filter:

Range("$A$1:$C$6").AutoFilter Field:=1, Criteria1:="4"
  • The Range part is the range that is being filtered. Note that if you have multiple sheets you want to call out the range like "'Sheet1'!$A$1:$C$6" instead of just "$A$1:$C$6".

  • The Field number is the column you are sorting by. In my example field 1 is column A because A was the first column I selected for my range. Had I selected something like "$B$1:$C$6" instead for my range, then Field 1 would be column B.

  • The Criteria1 is what to filter the Field by. Note that the criteria can be greater than, less than, etc. So you can have Criteria1:="<4" and it will perform as expected.

Attaching code to a button

I can attach the above code to a button by dragging a shape like a rectangle onto my sheet, right clicking the shape, and click on Assign Macro..

Then I just select the sub name that I created, which is Filter1 in this case.

Now you can do the same thing for your filters, changing the criteria and ranges. Also repeat for the number of filters you want to do.

  • That works great so far! Thank you so much but do you know I would write the criteria for filtering the dates? how would I go about typing greater than 5 years old from today or between 1 and 3 years old from today – Phazerman Jun 23 '17 at 17:27
  • @Phazerman You could add an "assist column" that is eventually hidden. In that column you could do =Today()-C2 where C2 is the date being referenced if that makes sense – Eric F Jun 23 '17 at 17:29
  • that makes sense, I just have to figure out how to make it work for me now – Phazerman Jun 23 '17 at 17:41
  • @Phazerman Good luck. Ask if you have any other questions – Eric F Jun 23 '17 at 17:46
  • That was certainly extremely helpful to me, say for example i have "03/05/2009" in cell K5 how would i write the code to have it show up as greater than 5 years. Could I write something like "IF K5<=Today()-1825" (1825 being the number of days in 5 years)? – Phazerman Jun 23 '17 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.