VBA time filter help

[2avrham]

Hi,

I have date column in the next format:

Complete_date

te

5/30/2018 6:44

5/20/18 1:51 PM

5/20/18 1:50 PM

5/20/18 1:52 PM

<colgroup><col width="131" style="width: 98pt; mso-width-source: userset; mso-width-alt: 4664;"> <tbody> </tbody>

I wonder how can I use VBA code that will run on my date column according to the next "rules"
1. Data should be filtered from the same date you click on it 08:00AM and take all dates from the last 24h. for example if im clicking on it now (my local time is 15:15 10/6/18) I will get all rows from 08:00am 9/6/18 - 08:00am 10/6/18.
2. all "null"\empty row should be retrieve as well.

Any idea how can I do this?

Thanks!

 

[Peter_SSs]

See if this does what you want. If not, more details/examples please.
Assuming data is in column A, starting in row 1 (header)

Sub Filter_Dates()
  Dim dStart As Date, dEnd As Date, dTest As Date
  Dim Filtervals As String
  Dim c As Range, DateRange As Range
  
  dEnd = Date + 8 / 24
  dStart = DateAdd("d", -1, dEnd)
  Filtervals = "="
  Set DateRange = Range("A1", Range("A" & Rows.Count).End(xlUp))
  For Each c In DateRange
    If IsDate(c.Value) Then
      dTest = CDate(c.Value)
      If dTest >= dStart And dTest <= dEnd Then Filtervals = Filtervals & "|" & c.Value
    End If
  Next c
  DateRange.AutoFilter Field:=1, Criteria1:=Split(Filtervals, "|"), Operator:=xlFilterValues
End Sub

 

[2avrham]

Thanks but something is wrong..
I have checked below dates and after I ran the code I did not get any result.

from this list:

6/9/2018 14:47

6/9/2018 10:22

6/9/2018 16:46

6/9/2018 16:56

6/9/2018 10:44

6/9/2018 15:26

6/9/2018 16:23

6/9/2018 11:04

6/9/2018 15:46

6/9/2018 16:38

6/9/2018 11:29

6/9/2018 17:01

6/9/2018 17:33

6/9/2018 11:37

6/9/2018 15:48

6/9/2018 16:44

6/6/2018 3:42

6/7/2018 4:54 I expected to get: 6/9/2018 14:47 6/9/2018 10:22 6/9/2018 16:46 6/9/2018 16:56 6/9/2018 10:44 6/9/2018 15:26 6/9/2018 16:23 6/9/2018 11:04 6/9/2018 15:46 6/9/2018 16:38 6/9/2018 11:29 6/9/2018 17:01 6/9/2018 17:33 6/9/2018 11:37 6/9/2018 15:48 6/9/2018 16:44 <tbody> </tbody><colgroup><col></colgroup> data from: 6/9/2018 08:00am till: 6/10/2018 08:00am

<tbody>
</tbody><colgroup><col></colgroup>

 

[Peter_SSs]

Thanks but something is wrong..
I have checked below dates and after I ran the code I did not get any result.

When you say you didn't get any result, do you mean ..
a) All rows were still visible, or
b) All rows were hidden?

I'm not sure why that is not working for you, but looking back now, perhaps I misread your post. You wrote "I have date column in the next format". I took that to meant in text format. If that is not what you meant, what does "next format" mean?

Here is my test data (note that my dates are in d/m/y format but I don't think that should be the problem)
Column A is formatted as Text (values manually entered)

Book1
	A
1	Date Time
2	9/6/2018 2:00
3	9/6/2018 5:00
4	9/6/2018 8:00
5
6	9/6/2018 11:00
7	9/6/2018 14:00
8	9/6/2018 17:00
9	9/6/2018 20:00
10	9/6/2018 23:55
11	10/6/2018 2:00
12	10/6/2018 5:00
13
14
15	10/6/2018 8:00
16	10/6/2018 11:00
17	10/6/2018 14:00
18
19	10/6/2018 17:00
20	10/6/2018 20:00
21	10/6/2018 23:00
22	11/6/2018 2:00
23	11/6/2018 5:00
24
Filter (2)

.. and here is my sheet after running the code on 10 June at about 11pm. Did I get the result you expected?

Book1
	A
1	Date Time
4	9/6/2018 8:00
5
6	9/6/2018 11:00
7	9/6/2018 14:00
8	9/6/2018 17:00
9	9/6/2018 20:00
10	9/6/2018 23:55
11	10/6/2018 2:00
12	10/6/2018 5:00
13
14
15	10/6/2018 8:00
18
24
Filter (2)