How to filter rows by date

I am new to VBA Excel. I want to select all of the values in a particular column whose associated dates in another column are within a certain range.

My foray into AutoFilter does not seem to be going well. This code filters for dates in the correct range but I cannot get it to include the first row:

Columns("B:B").AutoFilter Field:=1, _
    Criteria1:=">=" & startDate, _
    Operator:=xlAnd, _
    Criteria2:="<=" & endDate, _
    VisibleDropDown:=False

I am not clear about the use of the "Field" parameter. Also, the MS documentation claims that "Field" is an optional parameter of type Variant that nevertheless holds an Integer but the Excel VBA editor complains if I do not use it.

Answers


The field is the column which you want to apply the filter to. Since you are working with a single column (e.g. Columns("B:B")) this has to be 1. i.e. column B is the 1 and only column within column B so it is Field:=1.

[Optional] may not be the best way to describe the field parameter of the AutoFilter Method but since you can use .AutoFilter by itself with no parameters to reset the filters, strictly speaking all parameters are optional. However, it is not optional when you are applying criteria and the documentation should probably reflect that.

with Columns(2)
    'turn .AutoFilter on
    .AutoFilter Field:=1, Criteria1:=">=" & startDate, _
        Operator:=xlAnd, Criteria2:="<=" & endDate, _
        VisibleDropDown:=False
    'do stuff here
    'turn .AutoFilter off
    .AutoFilter
End With

You can add another autofilter to further refine your results as in :

''1st criteria for the dates

    Sheets("Your sheet").range("Your range").autofilter field =1,Criteria1:=">=" & startDate, Operator:=xlAnd, Criteria2:="<=" & endDate 

''2nd Criteria for text in field 2 for eg

    Sheets("Your sheet").range("Your range").autofilter field =2,Criteria1:="text" 

Need Your Help

Finite State Machine with Guards in PHP?

php state-machine

Does anybody know a finite state machine that has guard feature in PHP ?

Google Big Query - Error: 37.1 - 38.24: The JOIN operator's right-side table must be a small table

sql join bigdata google-bigquery

I got this error message when trying execute this SQL query into Google Big Query.