Sub AutoFilter_Date_Examples()
'Examples for filtering columns with DATES
Dim lo As ListObject
Dim iCol As Long
'Set reference to the first Table on the sheet
Set lo = Sheet1.ListObjects(1)
'Set filter field
iCol = lo.ListColumns("Date").Index
'Clear Filters
lo.AutoFilter.ShowAllData
With lo.Range
'Single Date - Use same date format that is applied to column
.AutoFilter Field:=iCol, Criteria1:="=1/2/2014"
'Before Date
.AutoFilter Field:=iCol, Criteria1:="<1/31/2014"
'After or equal to Date
.AutoFilter Field:=iCol, Criteria1:=">=1/31/2014"
'Date Range (between dates)
.AutoFilter Field:=iCol, _
Criteria1:=">=1/1/2014", _
Operator:=xlAnd, _
Criteria2:="<=12/31/2015"
End Sub
Multiple Date Groups
The following macro contains examples of how to filter for multiple date groups. This is the same as selecting specific years, months, days, hours, minutes from the list box in the filter drop-down menu.
For these filters we set the Operator parameter to xlFilterValues. We use Criteria2 (not Criteria1) to specify an Array of items using the Array function.
This is a special patterned array where the first number is the time period (year, months, days, etc.). The second number is the last date in the range. The macro below contains examples and further explanation.
Sub AutoFilter_Multiple_Dates_Examples()
'Examples for filtering columns for multiple DATE TIME PERIODS
Dim lo As ListObject
Dim iCol As Long
'Set reference to the first Table on the sheet
Set lo = Sheet1.ListObjects(1)
'Set filter field
iCol = lo.ListColumns("Date").Index
'Clear Filters
lo.AutoFilter.ShowAllData
With lo.Range
'When filtering for multiple periods that are selected from
'filter drop-down menu,use Operator:=xlFilterValues and
'Criteria2 with a patterned Array. The first number is the
'time period. Second number is the last date in the period.
'First dimension of array is the time period group
'0-Years
'1-Months
'2-Days
'3-Hours
'4-Minutes
'5-Seconds
'Multiple Years (2014 and 2016) use last day of the time
'period for each array item
.AutoFilter Field:=iCol, _
Operator:=xlFilterValues, _
Criteria2:=Array(0, "12/31/2014", 0, "12/31/2016")
'Multiple Months (Jan, Apr, Jul, Oct in 2015)
.AutoFilter Field:=iCol, _
Operator:=xlFilterValues, _
Criteria2:=Array(1, "1/31/2015", 1, "4/30/2015", 1, "7/31/2015", 1, "10/31/2015")
'Multiple Days
'Last day of each month: Jan, Apr, Jul, Oct in 2015)
.AutoFilter Field:=iCol, _
Operator:=xlFilterValues, _
Criteria2:=Array(2, "1/31/2015", 2, "4/30/2015", 2, "7/31/2015", 2, "10/31/2015")
'Set filter field
iCol = lo.ListColumns("Date Time").Index
'Clear Filters
lo.AutoFilter.ShowAllData
'Multiple Hours (All dates in the 11am hour on 1/10/2018
'and 11pm hour on 1/20/2018)
.AutoFilter Field:=iCol, _
Operator:=xlFilterValues, _
Criteria2:=Array(3, "1/10/2018 13:59:59", 3, "1/20/2018 23:59:59")
End With
End Sub
Dynamic Dates in Period Examples
The following macro contains examples for dates in specific periods. This is the same as selecting the preset filter items from the Date Filters sub menu.
For these filters we set the Operator parameter to xlFilterDynamic. We set Criteria1 to a constant that represents the dynamic date period option. The constants are listed below.
Sub AutoFilter_Dates_in_Period_Examples()
'Examples for filtering columns for DATES IN PERIOD
'Date filters presets found in the Date Filters sub menu
Dim lo As ListObject
Dim iCol As Long
'Set reference to the first Table on the sheet
Set lo = Sheet1.ListObjects(1)
'Set filter field
iCol = lo.ListColumns("Date").Index
'Clear Filters
lo.AutoFilter.ShowAllData
'Operator:=xlFilterDynamic
'Criteria1:= one of the following enumerations
' Value Constant
' 1 xlFilterToday
' 2 xlFilterYesterday
' 3 xlFilterTomorrow
' 4 xlFilterThisWeek
' 5 xlFilterLastWeek
' 6 xlFilterNextWeek
' 7 xlFilterThisMonth
' 8 xlFilterLastMonth
' 9 xlFilterNextMonth
' 10 xlFilterThisQuarter
' 11 xlFilterLastQuarter
' 12 xlFilterNextQuarter
' 13 xlFilterThisYear
' 14 xlFilterLastYear
' 15 xlFilterNextYear
' 16 xlFilterYearToDate
' 17 xlFilterAllDatesInPeriodQuarter1
' 18 xlFilterAllDatesInPeriodQuarter2
' 19 xlFilterAllDatesInPeriodQuarter3
' 20 xlFilterAllDatesInPeriodQuarter4
' 21 xlFilterAllDatesInPeriodJanuary
' 22 xlFilterAllDatesInPeriodFebruray <-February is misspelled
' 23 xlFilterAllDatesInPeriodMarch
' 24 xlFilterAllDatesInPeriodApril
' 25 xlFilterAllDatesInPeriodMay
' 26 xlFilterAllDatesInPeriodJune
' 27 xlFilterAllDatesInPeriodJuly
' 28 xlFilterAllDatesInPeriodAugust
' 29 xlFilterAllDatesInPeriodSeptember
' 30 xlFilterAllDatesInPeriodOctober
' 31 xlFilterAllDatesInPeriodNovember
' 32 xlFilterAllDatesInPeriodDecember
With lo.Range
'All dates in January (across all years)
.AutoFilter Field:=iCol, _
Operator:=xlFilterDynamic, _
Criteria1:=xlFilterAllDatesInPeriodJanuary
'All dates in Q2 (across all years)
.AutoFilter Field:=iCol, _
Operator:=xlFilterDynamic, _
Criteria1:=xlFilterAllDatesInPeriodQuarter2
End With
End SubDim daycheck As Date
' Date format and offset date
daycheck = Format(DateAdd("d", -15, Now()), "m-d-yyyy")
'
'Debug.Print daycheck
'
Dim lo As ListObject
Dim iCol As Long
Dim iCol2 As Long
Dim iCol3 As Long
'Set reference to the first Table on the sheet
Set lo = Sheet1.ListObjects(1)
'Clear Filters
lo.AutoFilter.ShowAllData
'Set filter field
iCol2 = lo.ListColumns("Submit Date").Index
' 'Blank cells - set equal to nothing
lo.Range.AutoFilter Field:=iCol2, Criteria1:="="
'Set filter field
iCol = lo.ListColumns("Issue Date").Index
With lo.Range
'Before Date
.AutoFilter Field:=iCol, _
Criteria1:="<" & daycheck
End With
'Set filter field
iCol3 = lo.ListColumns("Submission Title").Index
' 'Blank cells - set mpt equal to veriable
lo.Range.AutoFilter Field:=iCol3, Criteria1:="<>cancelled"Sub FilterDates()
Dim StartDate As Long, EndDate As Long
StartDate = DateSerial(Year(Date), Month(Date), Day(Date) - 7)
EndDate = DateSerial(Year(Date), Month(Date), Day(Date) + 7)
ThisWorkbook.Worksheets("Sheet1").ListObjects("Table1").Range.AutoFilter Field:=11, _
Criteria1:=">=" & StartDate, _
Operator:=xlAnd, _
Criteria2:="<=" & EndDate
End Sub
沒有留言:
張貼留言