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.
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"