CODEWITHSUNDEEP
excelvbafilterexcel-formulaautofilter
Asadasan
Asadasan

Reputation: 3

Filter data according to today's date and specific time range

I have a set of data that refreshed everyday on my excel sheet. I need to automatically filter the system date to today's date on column E and system time on column F between time of 0730 till 1930 via excel VBA daily. How should I do this?

I have tried using

Sub DateNTimeFilter()

Dim DateToday As Date
DateToday = Today()

Selection.AutoFilter
ActiveSheet.Range("E1:E").Autofilter Field=5, Operator:=x1Filtervalues, Criteria:=TodayDate

Upvotes: 0

Views: 3164

Answers (1)

Plutian
Plutian

Reputation: 2309

There are a number of issues with the code you posted.

All cleaned up it should look something like this:

Sub DateNTimeFilter()

Sheet1.Range("A:E").AutoFilter Field:=5, Operator:=xlFilterValues, Criteria1:=Format(Date,"dd/mm/yyyy")

End Sub

First of all Today() isn't a standard VBA function unless you've coded one. Use Date instead.

You also dim DateToday, but use TodayDate in your autofilter.

You also start your autofilter twice, once for the selection, once for your activesheet rangeE1:E. The first bit is redundant, so it can be left out.

Your range E1:E is an incorrect range, either use E:E or leave it out altogether. Since you use field:=5 already indicating column 5 (E).

You misspelled xlFilterValues, and forgot to specify Criteria1 as you always need to specify criteria 1 or 2.

If all you're using is todays date, no calculations need to be made, so you can insert this directly into your autofilter. The only thing I would add is a format statement to clean it up and match the way your dates are written.

EDIT: To add start and end times to this is a bit more challenging than this, but the below should do it.

Sub DateNTimeFilter()
Dim in_time As Double, out_time As Double

in_time = Date + TimeValue("07:30")
out_time = Date + TimeValue("19:30")

Sheet1.Range("A:A").AutoFilter Field:=1, Operator:=xlAnd, Criteria1:=">=" & in_time, Criteria2:="<=" & out_time

End Sub

Upvotes: 1

Related Questions