Filter criteria range from two cells including in VBA macro

[Waving Not Drowning]

Greetings people,

Many times visitor, first time poster.

I currently have a project where I would like to set up a VBA macro where I can fetch a start date and a end date into a criteria range filter.

The start and end date is in a separate sheet "indata". The list I want to apply the criteria filter on is in a separate sheet, "filter_sheet".

current I have a rather rudimentary inbox function set up but ideally I wish to have it fetch the start and end date automatically from cells in indata sheet.

current inbox code for reference;

Sheets("filter_sheet").Select
ActiveSheet.Range("$A$1:$E$6980").AutoFilter Field:=1, Criteria1:= _
InputBox("Enter from date", "From Date", 1), Operator:=xlAnd, Criteria2:=InputBox("Enter end date", "To Date", 1)

Thanks

 

[Alex Blakenburg]

See if the below does what you want.
I have used created 2 Named Ranged on your indata sheet.
1) DateFrom
2) DateTo
If you do the same on your indata sheet then you shouldn't need to change the macro.

Note: The macro will need to be changed if you end up using an Excel Table on the filter sheet.

Sub FilterByDate()

    Dim shtCriteria As Worksheet, shtFilter As Worksheet
    Dim dateFrom As Long, dateTo As Long
    Dim rngFilter As Range
    Dim rowLast As Long, colLast As Long
    
    Set shtCriteria = Worksheets("indata")
    Set shtFilter = Worksheets("filter_sheet")
    
    With shtCriteria
        dateFrom = .Range("DateFrom").Value2
        dateTo = .Range("DateTo").Value2
    End With
    
    With shtFilter
        If .FilterMode = True Then
            .ShowAllData
        End If
        rowLast = .Cells(Rows.Count, "A").End(xlUp).Row
        colLast = .Cells(1, Columns.Count).End(xlToLeft).Column
        Set rngFilter = .Range(.Cells(1, "A"), .Cells(rowLast, colLast))
        
        rngFilter.AutoFilter Field:=1, Criteria1:=">=" & dateFrom, _
                                        Operator:=xlAnd, _
                                        Criteria2:="<=" & dateTo
    End With

End Sub

 

[Waving Not Drowning]

Hello Alex,

Pure genius. Thanks. Worked like a charm with a few tweaks. Just realized I had another "fixed" criteria as well, but was easy to insert in your solution.

Thanks
/Urban

 

[Alex Blakenburg]

You're welcome. Glad to hear you were able to tweak it.