Dim dteEndDate As Date
Dim sStartCriterion As String
Dim sEndCriterion As String
‘Get Date values
iStartMonth = Me.ComboBox1.ListIndex + 1
iStartYear = Me.ComboBox2.Value
‘Calculate date values and format as US Dates
dteStartDate = DateSerial(iStartYear, iStartMonth, 1)
dteEndDate = DateSerial(iStartYear, iStartMonth + 1, 1)
sStartCriterion = “>=” & Format(dteStartDate, “mm/dd/yyyy”)
sEndCriterion = “<” & Format(dteEndDate, “mm/dd/yyyy”)
FilterDatesassigns the values selected in the combo boxes to iStartMonthand iStartYear. The Me
keyword has been used to refer to the sheet containing the code, rather than the object name Sheet1.
This makes the code portable, which means it can be used in other sheet modules without worrying
about the name of the sheet.
iStartMonthuses the ListIndexproperty of ComboBox1to obtain the month as a number. Because
the ListIndex is zero-based, 1 is added to give the correct month number. The DateSerial function
translates the year and month numbers into a date and assigns the date to dteStartDate. The second
DateSerialfunction calculates a date that is one month ahead of dteStartDateand assigns it to
The Format function is used to turn dteStartDate and dteEndDateback into strings in the U.S. date
format of mm/dd/yyyy. The appropriate logical operators are placed in front, and the resulting strings
are assigned to sStartCriterion and sEndCriterion, respectively. FilterDates finally executes the
AutoFiltermethod on the table Table1, using the computed criteria.
Getting the Exact Date
Another tricky problem with AutoFilter occurs with dates in all language versions of Excel. The problem
arises when you want to get an exact date, rather than a date within a range of dates. In this case,
AutoFilter matches your date with the formatted appearance of the dates in the worksheet, not the
underlying date values.
Excel holds dates as numeric values equal to the number of days since Jan 1, 1900. For example, Jan 1,
2007 is held as 39,083. When you ask for dates greater than or equal to Jan 1, 2007, Excel looks for date
serial numbers greater than or equal to 39,083. However, when you ask for dates equal to Jan 1, 2007,
Excel does not look for the numeric value of the date. Excel checks for the string value “Jan 1, 2007” as
it appears formatted in the worksheet and as it is returned by the Textproperty of the Rangeobject.
Chapter 6: Data Lists