首页 > 代码库 > 【VBA研究】工作表自己主动筛选模式检測

【VBA研究】工作表自己主动筛选模式检測

2024-10-21 20:41:02   210人阅读

作者：iamlaosong

用VBA程序处理Excel数据文件。用户的数据文件有时处于自己主动筛选模式，往往导致数据处理不对。为此，须要检測工作表是否处于该模式，假设是，则去掉自己主动筛选。语句例如以下：

        If ActiveSheet.AutoFilterMode = True Then Selection.AutoFilter

这个语句一般仅仅用于去掉自己主动筛选，尽管Selection.AutoFilter也能够加上自己主动筛选，但筛选位置却可能在当前单元格处，所以要注意。加自己主动筛选前，现将单元格定位到字段标题处。然后用例如以下语句：

        If ActiveSheet.AutoFilterMode = False Then Selection.AutoFilter


假设检測其他非活跃的工作表，能够用以下语句：

If Worksheets("sheet1").AutoFilterMode = True Then 

Worksheets("sheet1").Range("A1").AutoFilter 


附：
Range.AutoFilter Method
Excel Developer Reference
 
Filters a list using the AutoFilter.

Syntax

expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)

expression   An expression that returns a Range object.

Parameters

NameRequired/OptionalData TypeDescription
FieldOptionalVariantThe integer offset of the field on which you want to base the filter (from the left of the list; the leftmost field is field one).
Criteria1OptionalVariantThe criteria (a string; for example, "101"). Use "=" to find blank fields, or use "<>" to find nonblank fields. If this argument is omitted, the criteria is All. If Operator is xlTop10Items, Criteria1 specifies the number of items (for example, "10").
OperatorOptionalXlAutoFilterOperatorOne of the constants of XlAutoFilterOperator specifying the type of filter.
Criteria2OptionalVariantThe second criteria (a string). Used with Criteria1 and Operator to construct compound criteria.
VisibleDropDownOptionalVariantTrue to display the AutoFilter drop-down arrow for the filtered field. False to hide the AutoFilter drop-down arrow for the filtered field. True by default.

Return Value
Variant

Remarks

If you omit all the arguments, this method simply toggles the display of the AutoFilter drop-down arrows in the specified range.

Example

This example filters a list starting in cell A1 on Sheet1 to display only the entries in which field one is equal to the string "Otis". The drop-down arrow for field one will be hidden.

Visual Basic for Applications
Worksheets("Sheet1").Range("A1").AutoFilter _
    field:=1, _
    Criteria1:="Otis", _
    VisibleDropDown:=False



【VBA研究】工作表自己主动筛选模式检測


联系
我们