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 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

I presume you mean filter, not sort. if so, that sounds very much like the crux of the problem. It sounds like the autofilter is coming off and not setting again. Is that using the most recent code or earlier code?
If you use the most recent code, you should only have that in the module behind the sheet. No other code is required.

Me writing the code may help. Please advise
- column in which you have your dates
- column in which you have the TRUE/FALSE formula

Another thing for you to double-check:
- make sure your formula relating to the value in A2 is in the same row as A2 =AND(A2>=$D$1,A2<=$E$1)

Alternatively, put a copy of your WB (with all sensitive data changed or removed) in DropBox, OneDrive, etc and send me a link via personal message (click my forum name, click Private message).

 

[buyers]

I think I did it right? URL is https://www.dropbox.com/s/vhb9lmb8fozoitj/Test- Group Master List.xlsm?dl=0

It appears to kind of be working, though 2014 shows all and when you click the others it hides the first 5 lines of the document for some reason?

 

[Steve_R]

I see the problem

You have entries in the column of than the formula that returns TRUE or FALSE. This is creating additional items other than TRUE and FALSE on the filter list. You have your end date calculation, "End date" heading and the calculated date appearing as options. You don't need these. The only things in the column should be the formula that returns TRUE or FALSE.

1) Cut Paste your end date forumula and heading into column A (formulas will adjust to the change)
2)Clear everything in column B above B7 (i.e. B1:B6 should all be blank cells)
3) Alt/F11 - right-click and choose the option to remove regular Modules (un-needed code)
4) I did not take into account before your headings may hide. The following works in the module under BIF 2015 (after getting rid of the existing module

Note E3, not D3

Note: Not Intersect(ActiveCell, Range("E3")) Is Nothing (i.e. double negative - means if E3 changes)

Note now reads TFTestCol = "$B$7:$B$14670" so it doesn't collapse your headings

14670 in the above is an arbitrary number to allow for additional records. It is relatively easy to change 14670 into a dynamic number that is the last row of your data if you feel you need that.
It is also to generate the formula in column B if a new record is entered.

Option Explicit

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

DateCol = "C:C" 'change to match your date column
TFTestCol = "$B$7:$B$14670" ' 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

 

[buyers]

Perfect, this works amazing!!!! Last question, if I wanted to filter say the "status" column within say 2015, it reverts to all, any way to change this? If not no big. Can't thank you enough for your help!

 

[Steve_R]

I missed the filtering was on a datatable. I dont have previous experience automating filtering of datatables - only tables of data and needed to hunt the method. After sleeping on it:

Code attached seems to solve the problem but first you need to do a few tweaks.

1) Turn off any autofilter
2) Resize your table to include column B. In other words include TRUE/FALSE calculations in the datatable (but still hide after)
3) Add 'All' to your data validation list (so you can select all years)

Code no longer switches autofilter off but only changes Criterea1 in column(1) of Table3
There is no longer a need to refer to a range of cells (taken care of by dimensions of Table3

I left Sub GetListObjectNames below the Worksheet Change code. It is not called but I thought you may find it handy to fetch a Table name if you put the code on other sheets.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("E3")) Is Nothing Then
' Change "E3" to the cell the user completes the year in
Const tlbName = "Table3" 'update to name of table name on relevant sheet

Dim DateCol
Dim MinYear
Dim MaxYear

DateCol = "C:C" 'change to match your date column

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
        .ListObjects(tlbName).Range.Columns(1).AutoFilter Field:=1, Criteria1:="TRUE"
        
    Else
       .ListObjects(tlbName).Range.Columns(1).AutoFilter Field:=1
          
               
    End If
End With
End If

End Sub

Sub GetListObjectNames()

  Dim ws As Worksheet
    Dim lo As ListObject
    Dim rng As Range

    Set ws = ActiveSheet

    For Each lo In ws.ListObjects
         'Debug.Print lo.Name
         MsgBox lo.Name
    Next lo
    
End Sub

 

[buyers]

I get a debug error on this line? .ListObjects(tlbName).Range.Columns(1).AutoFilter Field:=1, Criteria1:="TRUE"

 

[buyers]

Also, trying to run previous code on another sheet and get the below debug error?

MinYear = Year(WorksheetFunction.Min(ActiveSheet.Range(DateCol)))
MaxYear = Year(WorksheetFunction.Max(ActiveSheet.Range(DateCol)))

 

[buyers]

Here is a new link, note I have both worksheets in seperate tabs but code should work the same. Thank you
https://www.dropbox.com/s/vhb9lmb8fozoitj/Test- Group Master List.xlsm?dl=0

 

[Steve_R]

Also, trying to run previous code on another sheet and get the below debug error?
MinYear = Year(WorksheetFunction.Min(ActiveSheet.Range(DateCol)))
MaxYear = Year(WorksheetFunction.Max(ActiveSheet.Range(DateCol)))

I can't see the error but:
1) Check DateCol values refer the column in which effective dates are recorded (meanwhile I
2) Potentially, change
MinYear = Year(WorksheetFunction.Min(ActiveSheet.Range(DateCol)))
to
MinYear = Year(WorksheetFunction.Min(Sheets("{releventSheetName}").Range(DateCol)))

or Add 'Application.' into the code
MinYear = Year(Application.WorksheetFunction.Min(ActiveSheet.Range(DateCol)))
MinYear = Year(Application.WorksheetFunction.Min(Sheets("{releventSheetName}").Range(DateCol)))

NOTE above are all OTOH and untested.

If they don't work I will change the method to read the max and min from column 2 (effective date) of the table.

 

[buyers]

Ya, that didn't seem to work