vb macro to filter date

Question

I am working on a macro so i can pull data from today and previous day.  Before I just filtered data using data filter & between where I manually inserted date and time.  I have to go back and pull data from the report from between 5pm previous day and 5 am current date.  If I manualy insert date and time my macro would look like this:

ActiveSheet.Range("$A$1:$G$193").AutoFilter Field:=4, Criteria1:= _

        ">=2/18/2012 17:00", Operator:=xlAnd, Criteria2:="<=2/19/2012 5:00"

but i want to insert right code so it will apply to any date, something like today -1 17:00 and today 5:00.

Any idea how I can do it?

Thanks

Answer 1

Turn off AutoFilter.

If you have any Conditional Formats set, select Conditional Formating -> Manage rules and then select and delete any existing rules you have attempted to create and are not working properly.  (Alternatively Select Clear rules in lieu of Manage Rules.)

Select all data to which Conditional Format is to be applied. (In your case from previous posts I assume this is A2:G153**. Do not include column headers**.)

Select Conditional Formating -> New Rule

Select Use formula to determine which cells to format.

Enter the following formula in the field under **Format values where this formula is true.**Edit the column Id in the formula to the column Id that contains the Priority. (I have used column C.)  Note the $ sign so that the column is absolute but no dollar sign preceding the row because the row needs to be relative.

=$C2=1

Click Format button and then Font tab and select Bold and select required color.

Click OK and OK again to complete.

Explanation:

Conditional format formula is entered as if it is being applied to only one cell in the first row and Excel looks after applying that formula to the remaining selected cells.

The absolute addressing of the column (with the $ sign) is so that as the formatting is applied across the columns, the column being tested for value 1 is locked to the same column and will not change across the other columns.

The row number needs to change down the data so it is relative (with no $ sign).

Hope this helps. Feel free to get back to me. However, if you do, please answer the following questions.

What is the range of data to which the conditional formatting is to be appied?

What column contains the priority.

Answer 2

Ron Rosenfield wrote:

Another way of dealing with this issue (and make autofilter independent of the date format)  is to use something like:

rSrc.AutoFilter Field:=1, _

    Criteria1:=Array(4, ">=" & dt1), _

    Operator:=xlAnd, _

    Criteria2:=Array(4, "<=" & dt2)

End Sub

where dt1 and dt2 are the two date/times (Dim's as type Date).  If one is only using a single date, one must use Criteria2.

Unfortunately in a d/m/y date format region the above does not work if the dates are dimensioned as date variables. See the following test code.

Sub AutoFilterDateTime_2()

    Dim dt1 As Date     'Dimension variables as date

    Dim dt2 As Date     'Dimension variables as date

    dt1 = Date - 1 + #5:00:00 PM#       'Yesterday at 5:00 PM

    dt2 = Date + 1 + #5:00:00 AM#       'Tomorrow at 5:00 AM

    ActiveSheet.AutoFilterMode = False  'Turns off AutoFilter

    '*********************************************************

    'Code between the asterisk lines failed in d/m/y date format region

    'Range("A1").AutoFilter Field:=1, _

            Criteria1:=Array(4, ">=" & dt1), _

            Operator:=xlAnd, _

            Criteria2:=Array(4, "<=" & dt2)

    '*********************************************************

    'Following code works fine in d/m/y/ date format region

    Range("A1").AutoFilter Field:=1, _

            Criteria1:=Array(4, ">=" & Format(dt1, "mm/dd/yyyy hh:mm")), _

            Operator:=xlAnd, _

            Criteria2:=Array(4, "<=" & Format(dt2, "mm/dd/yyyy hh:mm"))

 End Sub

However, an interesting thing is that if the variables dt1 and dt2 are dimensioned as Double so that the dates are in Serial Format then it works fine without the Format command. See the following code. My other examples can be converted to this also.

Sub AutoFilterDateTime_3()

    Dim dt1 As Double     'Dimension variables for dates as double

    Dim dt2 As Double     'Dimension variables for dates as double

    dt1 = Date - 1 + #5:00:00 PM#       'Yesterday at 5:00 PM

    dt2 = Date + 1 + #5:00:00 AM#       'Tomorrow at 5:00 AM

    'Code works fine with dates in Serial Format

    Range("A1").AutoFilter Field:=1, _

            Criteria1:=Array(4, ">=" & dt1), _

            Operator:=xlAnd, _

            Criteria2:=Array(4, "<=" & dt2)

 End Sub

Seems to me that this solution should work irrespective of the regional date format. The reason that it works is that the code has to translate the date into serial format and this is where the problem is in not recognizing the d/m/y format to do this. However, if it is already in serial format then no doubt it is recognized as such and problem solved.

Answer 3

OssieMac wrote:

If you want to be able to manipulate the dates like today -1  or today + 1 etc then the variables should be Date variables; not strings as per answer by TasosK.

[....]

    Dim dt1 As Date     'Dimension variables as date

    Dim dt2 As Date     'Dimension variables as date

    dt1 = Date - 1 + #5:00:00 PM#       'Yesterday at 5:00 PM

    dt2 = Date + 1 + #5:00:00 AM#       'Tomorrow at 5:00 AM

    ActiveSheet.UsedRange.AutoFilter Field:=1, _

            Criteria1:=">=" & Format(dt1, "mm/dd/yyyy hh:mm"), _

            Operator:=xlAnd, _

            Criteria2:="<=" & Format(dt2, "mm/dd/yyyy hh:mm")

In either case, there is no need to use Format.  Simply:

    Range("A1:G193").AutoFilter Field:=4, _

            Criteria1:=">=" & dt1, Operator:=xlAnd, Criteria2:="<=" & dt2

If you want to dynamically select the last row of A:G, ActiveSheet.UsedRange is too expansive, IMHO.  I would do the following:

   Dim myRng as Range

   ' ...other declarations and code...

   Selection.AutoFilter        ' remove autofilter so xlDown can be found

   Set myRng = Range("A1",Range("G1").End(xlDown))

   Range("A1:G193").AutoFilter Field:=4, _

            Criteria1:=">=" & dt1, Operator:=xlAnd, Criteria2:="<=" & dt2

End(xlDown) finds the last non-empty cell after G1.  We assume that G1 contains a title or data, and there is at least one empty cell following the data starting in G1 and any subsequent unfiltered data (or that the data starting in G1 extends to G1048576 ;->).

Answer 4

I have edited this post since original posting to incorporporate better programming solutions that I have discussed in further posts on this thread.

If you want to be able to manipulate the dates like today -1  or today + 1 etc then the variables should be Date variables; not strings as per answer by TasosK.

Note that in VBA, Date is the equivalent of worksheet function TODAY().

Also note the use of Range("A1:G1"). in lieu of a fixed range. This method will apply Autofilter to all rows irrespective of how many and keeps the code dynamic to the amout of data.

Even though code posted by joeu2004 may work in an m/d/y format region, I believe that it is better to use code that will work in any region because you never know when it will be used outside of your own region.

Sub AutoFilterDateTime()

    Dim dt1 As Date     'Dimension variables as date

    Dim dt2 As Date     'Dimension variables as date

    dt1 = Date - 1 + #5:00:00 PM#       'Yesterday at 5:00 PM

    dt2 = Date + 1 + #5:00:00 AM#       'Tomorrow at 5:00 AM

    ActiveSheet.AutoFilterMode = False  'Turns off AutoFilter

    ActiveSheet.Range("A1:G1").AutoFilter Field:=1, _

            Criteria1:=">=" & Format(dt1, "mm/dd/yyyy hh:mm"), _

            Operator:=xlAnd, _

            Criteria2:="<=" & Format(dt2, "mm/dd/yyyy hh:mm")

End Sub

Answer 5

Hi,

try this...

1. column D: format as Custom Format  mm/dd/yyyy hh:mm
2. write this sample

Sub AutoFilterDateTime()

dt1 = "02/18/2012 17:00"

dt2 = "02/19/2012 05:00"

Range("D1:D193").AutoFilter Field:=1, _

Criteria1:=">=" & Format(dt1, "mm/dd/yyyy hh:mm"), Operator:=xlAnd, _

Criteria2:="<=" & Format(dt2, "mm/dd/yyyy hh:mm")

MsgBox "next...."

Range("A1").AutoFilter

End Sub