в 16:33, 26 мая 2010

2010-05-26T16:33:00Z

Admin: 1 версия

2010-05-26T12:47:51Z

1 версия

Новая страница

==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>

← Предыдущая	Версия 16:33, 26 мая 2010
(нет различий)

VBA/Excel/Access/Word/Excel/AutoFilter - История изменений

в 16:33, 26 мая 2010

Admin: 1 версия