Filtering column between 2 values, which source is in data vaulation.

[TomeK712]

Hi All,

I do have an issue with filtering a column between two values, which have a source in a different worksheet as a Data Valuation.

I've got a column with dates and times. I need to filter it up between values which are in format:

=TEXT(TODAY(), "dd-mm-yyyy")&" 10:00:00"
=TEXT(TODAY(), "dd-mm-yyyy")&" 11:00:00"
=TEXT(TODAY(), "dd-mm-yyyy")&" 12:00:00"
=TEXT(TODAY(), "dd-mm-yyyy")&" 13:00:00"

The date will be changing everyday, time range will be this same.

My VBA code is:

        If Worksheets("MENU").CheckBoxes("CheckBox1").Value = 1 Then
            Dim lngStart As Range, lngEnd As Range
                Set lngStart = Worksheets("MENU").Range("I17") 'first data valuation
                Set lngEnd = Worksheets("MENU").Range("K17") 'second data valuation
                Worksheets("INBOUND").Range("$A$1:$Y$2000").AutoFilter Field:=11, _
                Criteria1:=">=" & lngStart, _
                Operator:=xlAnd, _
                Criteria2:="<=" & lngEnd
        End If

When the macro is running with Range("I17") and ("K17") as a Data Valuation cell it doesn't work. As soon as I'll disable data valuation and leave the value as 10-08-2017 10:00:00 it works perfectly.

I don't know why my code is not reading the value from Data Valuation, I couldn't find a similar thread to my as well.

 

[mart37]

The TEXT function creates the dates as text.
Change the format in date.

 

[TomeK712]

The thing is I need to update the date every day, is there option to use a different formula to setup the cell as "dd-mm-yy hh:mm", where the date is 'live' and time is always this same?

 

[mart37]

=TODAY()+10/24

numbernotation - custom: dd-mm-yyyy hh:mm:ss;@

 

[TomeK712]

You're a star dude!
Thank you so much!