Use List Drop Down to sort date range in spreadsheet

[buyers]

After spending hours trying to figure this out, I'm hoping you all can help. I'd like to use a drop down list with they year (2014/2015/2016/2017) to filter my spreadsheet by a date range of say 2/1/15-1/1/16=2015, 2/1/16-1/1/17=2017, etc.

		2015
1/1/15	xyc	abc	ed	b
12/1/16	dkdjf	akljf	fakldj	falkdj
2/1/17	fdaklj	fjadjk	akdjf;	fdalkj

<tbody>
</tbody>

Would love anyone's help, thank you!!!

 

[Steve_R]

I do this using a 'helper' column.
First, set it up;

In one cell (C1 for my example) user enters the year "2009' for example
Another Cell (D1 for my examplel) contains a formula to get the first required date in the year (for example,'=DATE($C$1,1,1)' will give Jan 1st and the year returned as a date)
Another Cell (E1 in my example) contains the formula for the last date required in the year =DATE($C$1,12,31)
Next, in a column somewhere to the right of your data write a formula to return TRUE or FALSE if your dafalls bettween required dates - =AND(A8>=$D$1,A8<=$E$1)
Now set up autofilter on that column to hide show TRUE.

That will work the first time you set a year then apply the autofilter but you want that to happen automatically, so:

After setting up your autofilter, some VBA will make the filter change whenever the year cell is changed.
Right-Click the tab of the require sheet
Click View Code
Paste the following
Make sure you update the cell reference from C1.

Private Sub Worksheet_Change(ByVal Target As Range)


If Intersect(ActiveCell, Range("C1")) Is Nothing Then
' Change C1 to the cell the user completes the year in

On Error Resume Next ' will stop the code break if autofilter is off
ActiveSheet.AutoFilter.ApplyFilter
On Error GoTo 0

End If


End Sub

Note: above code is set to change the filter only when the year cell is changed so it will not annoy when adding new records (e.g. if you added a new record outside the date year or the filter it would immeidately hide that line.

You could modify the range to a column address if you wish for example

If Intersect(ActiveCell, Range("G:G")) Is Nothing Then

Would run the code when something in column G changes.


Set the width of the column that returns TRUE FALSE if you wish, put it well to the right out of sight to users or both. Protect, and hide year start and year end dates as you wish.

<colgroup><col width="35"><col span="2" width="75"></colgroup><tbody>
</tbody>

 

[Steve_R]

You may also need to toggle the filter on/off using code. A form control button with something like the following would do that.

Sub ToggleAutoFilter()

With ActiveSheet
        
            
        If .FilterMode = True Then
           .AutoFilterMode = Not .AutoFilterMode
        Else
        
        .Range("F:F").AutoFilter Field:=1, Criteria1:="TRUE"
        'Change "F:F" to the column in which you have your formula
        End If
    End With
End Sub

 

[buyers]

Thank you so much for your help!! I have the true/false filter set up but the coding is giving an error of "Compile error: Block If without End If" and is not updating the filter as I change the year in the list? Also, how do I save the code, do I just click exit? Really appreciate your help.

 

[buyers]

It also states when I go to exit that "The following features cannot be saved in macro-free workbooks: VB project, To save a file with these features, click no, and then choose a macro enabled file type in the file type list? Not sure what this means. Thank you.

 

[Steve_R]

When you save the workbook, all changes including VBA modules will be saved. The error you are receiving is due to accepting the default 'Save' option. You need to 'Save As' and choose 'Save as Type' "Excel Macro-Enabled Workbook (*.xlsm)"

The code tested fine at this end. Reading the structure of the posted, the lines that would normally produce that type of error look correct (for each I there is and END IF; for each With there is an end With and are ordered correctly). I can only guess you may have inadvertently missed pasting some of the code. So, Double-check you paste everything, including the lines
'Private Sub Worksheet_Change(ByVal Target As Range)'
on the first line and
'End Sub'
on the last line.

Ditto for 'Sub ToggleAutoFilter()' which should also finish with 'End Sub'


You have choices what to do with the ToggleAutoFilterSub.
1) Not use it at all - As people add new lines the new lines remain visible until the year cell changes. A disadvantage of that is there is no option for people to view all records.
2) Add more code that distinguishes when the year cell has something other than a year in the year cell and use that to toggle the filter off and if a yyear is in the cell, toggle it back on
3) A macro button on the sheet that lets the user decide whether or not to see all records without changing the year cell

Option 1) Use the first set of code only
If you decide on option 2, let me know.
Option 3) the second set of code to a regular module and create a macro button on the sheet. The two videos following will help

https://www.youtube.com/watch?v=rQYpRBJEH8w
Debra includes some explanation of the sub she uses as an example, you don't need to know that, just how to create a regular module in which to save the 'toggle' code

Do that first using the ToggleAutoFilter code then use the following to learn Macro button creation.


https://www.youtube.com/watch?v=4ji19G6rTm8

 

[Steve_R]

I had a little time to burn today so wrote the following for Option 2. If you decide to use it
-Make sure you copy all code including the lines 'Option Explicit' and 'End Sub'
-Right click on the name tab of the relevant worksheet and choose 'View Code'
-Delete existing code
-Paste

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(ActiveCell, Range("C1")) Is Nothing Then
' Change C1 to the cell the user completes the year in
Dim DateCol
Dim TFTestCol
Dim MinYear
Dim MaxYear

DateCol = "A:A" 'change to match your date column
TFTestCol = "F:F" ' change to match the column that tests
                  ' whether year is in the reqired range
MinYear = Year(WorksheetFunction.Min(ActiveSheet.Range(DateCol)))
MaxYear = Year(WorksheetFunction.Max(ActiveSheet.Range(DateCol)))

With ActiveSheet
    If Target.Value >= MinYear And Target.Value <= MaxYear Then
        .Range(TFTestCol).AutoFilter Field:=1, Criteria1:="TRUE"
    Else
        If .FilterMode = True Then
           .AutoFilterMode = Not .AutoFilterMode
        
        End If
    End If
End With
End If

End Sub

Don't forget Save as Macro enabled.

There are comments in the code to tell you what to change. Example I used column A as the data Date column. If you need to change to column B change the line
DateCol = "A:A"
to
DateCol = "B:B"

How it works:

The code triggers evtime you change change a value on the sheet but bails out straight away if you change anothing other than your filter year cell (now set at C1).
If your filter year changes:
-First it determines the minimum year in a date column you will need to amend (now set A:A)
-Next it determines the maximum year in the same date column
- If whatever is entered into the date cells is a number representing a year that falls between the minimum date year and maximum date year a filter is applied.
- If whatever is entered does not fall between minimum and maximum years, it switches the filter off (in other words, clear the year cell, enter N/A or anything else other than year that falls within the range of your records and the filter switches off).

Depending on the formula used a formula also works - Example '=YEAR(NOW())-1' works for my test data
If the year entered is between the minimum and maximum dates and has no matches, all records are filtered out (all FALSE so all hidden).
Sheet formula are ugly if you enter a string or formula that returns a string (#Value error). That is easily fixed by modifying the formula but there is nothing in the formula cells you need to see once you have it working. Instead of adding an error test, I suggest hiding or format white font on white background.

 

[buyers]

Ok, so I watched the videos which were helpful, but the drop down list of years is still not auto filtering. Couple questions, I first tried putting both sets of code under the sheet, didn't work, then after watching the first video I thought I may need to put them each under seperate modules, but then they both did not show up under macros. With this I thought I would ask, if I do need to do them under seperate modules do I need to do the "sub worksheet" and Run under the drop down year list, and Run the "auto filter" on the filter cell in the column with all the true/false. Tried it, and still nothing. I have double checked the code and I have it exactly as you illustrated. hmmmmm. I would insert the picture in the Thread, but not sure how to do this via URL. Thank you for your help!!

 

[buyers]

I also just realized that if I go to Macro, and "Run" when the list is 2015, it does sort them, but does not do anything when I do 2016, or 2017, even though I can see the true/false column changes, didn't know if this was important to know or not. Thank you

 

[Steve_R]

hmmmmm. I would insert the picture in the Thread, but not sure how to do this via URL. Thank you for your help!!

I'm around here and have not tried the following (do have lot of experience on Microsoft Forums answering uestions before they 'went web' only many years ago).

The simple way to include an image seems to be:
Save your file to Dropbox, OneDrive or whatever cloud service you use (make sure you use a public folder).
Copy the public link to the file
Click the 'Insert Image' Icon (square box with tree on the toolbar three from right when you draft a post)
Paste your link in the appropriate place
Click OK.