VBA/Excel/Access/Word/Excel/AutoFilter — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
|
Текущая версия на 15:47, 26 мая 2010
Содержание
- 1 determine whether a ListObject object AutoFilter is turned on or off by testing its ShowAutoFilter property.
- 2 filter a column to one of two customers, joined by the OR operator:
- 3 Filtering Based on Color or Icon
- 4 filters to show records
- 5 produces the top 10 revenue records:
- 6 returns all customers that started with the letters A through E:
- 7 Selecting a Dynamic Date Range Using AutoFilters
- 8 Selecting Multiple Values from a Filter
- 9 The AutoFilter object only exists when the AutoFilter feature is turned on. You can determine whether the Worksheet AutoFilter is active by using the value of the AutoFilterMode property
- 10 The fastest way to delete rows is provided by Excel"s AutoFilter feature:
- 11 There is a Filters collection associated with the AutoFilter object that holds a Filter object for each field in the AutoFilter
- 12 To clear the filter from the customer colum
- 13 To ensure that the worksheet AutoFilter is turned off
- 14 To find records that have a particular fill color, use an operator of xlFilterCellColor and specify a particular RGB value as the criteria.
- 15 To find records that have a particular font color, use an operator of xlFilterFontColor and specify a particular RGB value as the criteria.
- 16 To find records that have no conditional formatting icon, use an operator of xlFilterNoIcon and do not specify any criteria.
- 17 To find records that have no fill color, use an operator of xlFilterNoFill and do not specify any criteria.
- 18 To switch off a range AutoFilter, you use the AutoFilter method of the Range object with no parameters:
- 19 turn on/off the AutoFilter drop-downs:
- 20 turns off the drop-downs for Columns C, E, F, G, and H:
- 21 Use this code to turn off the AutoFilter drop-downs:
determine whether a ListObject object AutoFilter is turned on or off by testing its ShowAutoFilter property.
<source lang="vb">
Sub showFilter()
ActiveSheet.ListObjects("Table1").ShowAutoFilter = False
End Sub
</source>
filter a column to one of two customers, joined by the OR operator:
<source lang="vb">
Sub SimpleOrFilter()
Worksheets("SalesReport").Select Range("A1").AutoFilter Range("A1").AutoFilter Field:=4,Criteria1:="=A", Operator:=xlOr, Criteria2:="=B"
End Sub
</source>
Filtering Based on Color or Icon
<source lang="vb">
Sub FilterByIcon()
Worksheets("SalesReport").Select Range("A1").AutoFilter Range("A1").AutoFilter Field:=6, _ Criteria1:=ActiveWorkbook.IconSets(xl5ArrowsGray).Item(5),Operator:=xlFilterIcon
End Sub
</source>
filters to show records
<source lang="vb">
Sub SimpleFilter()
Worksheets("Sheet1").Select range("A1").autoFilter range("A1").autoFilter Field:=4, Criteria1:="=Agile Aquarium Inc."
End Sub
</source>
produces the top 10 revenue records:
<source lang="vb">
Sub Top10Filter()
" Top 12 Revenue Records Worksheets("SalesReport").Select Range("A1").AutoFilter Range("A1").AutoFilter Field:=6, Criteria1:="12",Operator:=xlTop10Items
End Sub
</source>
returns all customers that started with the letters A through E:
<source lang="vb">
Sub SimpleAndFilter()
Worksheets("SalesReport").Select Range("A1").AutoFilter Range("A1").AutoFilter Field:=4, _ Criteria1:=">=A", _ Operator:=xlAnd, Criteria2:="<=EZZ"
End Sub
</source>
Selecting a Dynamic Date Range Using AutoFilters
<source lang="vb">
Sub DynamicAutoFilter()
Worksheets("SalesReport").Select Range("A1").AutoFilter Range("A1").AutoFilter Field:=3,Criteria1:=xlFilterNextYear,Operator:=xlFilterDynamic
End Sub
</source>
Selecting Multiple Values from a Filter
<source lang="vb">
Sub MultiSelectFilter()
Worksheets("SalesReport").Select Range("A1").AutoFilter Range("A1").AutoFilter Field:=4, Criteria1:=Array("A", "C", "E","F", "H"),Operator:=xlFilterValues
End Sub
</source>
The AutoFilter object only exists when the AutoFilter feature is turned on. You can determine whether the Worksheet AutoFilter is active by using the value of the AutoFilterMode property
<source lang="vb">
Sub filter()
If ActiveSheet.AutoFilterMode Then MsgBox "Turned on" End If
End Sub
</source>
The fastest way to delete rows is provided by Excel"s AutoFilter feature:
<source lang="vb"> Sub DeleteRows3() Dim lLastRow As Long "Last row Dim rng As range Dim rngDelete As range "Freeze screen Application.ScreenUpdating = False "Insert dummy row for dummy field name Rows(1).Insert "Insert dummy field name range("C1").value = "Temp" With ActiveSheet .UsedRange lLastRow = .cells.SpecialCells(xlCellTypeLastCell).row Set rng = range("C1", cells(lLastRow, "C")) rng.AutoFilter Field:=1, Criteria1:="Mangoes" Set rngDelete = rng.SpecialCells(xlCellTypeVisible) rng.AutoFilter rngDelete.EntireRow.delete .UsedRange End With End Sub </source>
There is a Filters collection associated with the AutoFilter object that holds a Filter object for each field in the AutoFilter
<source lang="vb">
Sub Main()
With ActiveSheet.ListObjects(1) If .ShowAutoFilter Then With .AutoFilter.Filters(2) If .On Then MsgBox .Criteria1 End If End With End If End With
End Sub
</source>
To clear the filter from the customer colum
<source lang="vb">
Sub SimpleFilter1()
Worksheets("Sheet1").Select range("A1").autoFilter range("A1").autoFilter Field:=4
End Sub
</source>
To ensure that the worksheet AutoFilter is turned off
<source lang="vb">
Sub fileterAuto()
If ActiveSheet.AutoFilterMode Then ActiveSheet.autoFilter.range.autoFilter End If
End Sub
</source>
To find records that have a particular fill color, use an operator of xlFilterCellColor and specify a particular RGB value as the criteria.
<source lang="vb">
Sub FilterByFillColor()
Worksheets("SalesReport").Select Range("A1").AutoFilter Range("A1").AutoFilter Field:=6, Criteria1:=RGB(255, 0, 0), Operator:=xlFilterCellColor
End Sub
</source>
To find records that have a particular font color, use an operator of xlFilterFontColor and specify a particular RGB value as the criteria.
<source lang="vb">
Sub FilterByFontColor()
Worksheets("SalesReport").Select Range("A1").AutoFilter Range("A1").AutoFilter Field:=6,Criteria1:=RGB(255, 0, 0), Operator:=xlFilterFontColor
End Sub
</source>
To find records that have no conditional formatting icon, use an operator of xlFilterNoIcon and do not specify any criteria.
<source lang="vb">
Sub FilterByIcon()
Worksheets("SalesReport").Select Range("A1").AutoFilter Range("A1").AutoFilter Field:=6,Criteria1:=ActiveWorkbook.IconSets(xl5ArrowsGray).Item(5),Operator:= xlFilterNoIcon
End Sub
</source>
To find records that have no fill color, use an operator of xlFilterNoFill and do not specify any criteria.
<source lang="vb">
Sub FilterByFillColor()
Worksheets("SalesReport").Select Range("A1").AutoFilter Range("A1").AutoFilter Field:=6, _ Criteria1:=RGB(255, 0, 0), Operator:= xlFilterNoFill
End Sub
</source>
To switch off a range AutoFilter, you use the AutoFilter method of the Range object with no parameters:
<source lang="vb">
Sub autoFilter()
range("B3:D9").autoFilter
End Sub
</source>
turn on/off the AutoFilter drop-downs:
<source lang="vb">
Sub TurnOnAutoFilter()
" Turn on AutoFilters Worksheets("Sheet1").Select On Error Resume Next x = ActiveSheet.autoFilter.range.Areas.count If Err.Number > 0 Then ActiveSheet.range("A1").autoFilter End If On Error Resume Next
End Sub
</source>
turns off the drop-downs for Columns C, E, F, G, and H:
<source lang="vb">
Sub AutoFilterCustom()
range("A1").autoFilter Field:=3, VisibleDropDown:=False range("A1").autoFilter Field:=5, VisibleDropDown:=False range("A1").autoFilter Field:=6, VisibleDropDown:=False range("A1").autoFilter Field:=7, VisibleDropDown:=False range("A1").autoFilter Field:=8, VisibleDropDown:=False
End Sub
</source>
Use this code to turn off the AutoFilter drop-downs:
<source lang="vb">
Sub TurnOffAutoFilter()
Worksheets("Sheet1").Select On Error Resume Next x = ActiveSheet.autoFilter.range.Areas.count If Err.Number = 0 Then ActiveSheet.range("A1").autoFilter End If On Error Resume Next
End Sub
</source>