是否有一种方法可以在宏中包含一些允许停用自动筛选器的代码,然后运行实际的宏,然后再重新激活自动筛选器?
我之所以问这个问题,是因为我的原始宏在自动筛选器激活的情况下无法处理数据。
发布于 2017-08-31 06:39:18
ActiveSheet.AutoFilterMode = False
将ActiveSheet更改为所需的工作表/变量名
发布于 2017-08-31 07:08:27
禁用特定Worksheet
上的筛选器的
Workbook("WorkbookName").Worksheets("SheetName").AutoFilterMode = False
在特定的Worksheet
上显示所有数据(重置过滤准则)的
If Workbook("WorkbookName").Worksheets("SheetName").FilterMode Then
Workbook("WorkbookName").Worksheets("SheetName").ShowAllData
EndIf
Workbook
中的通过每个 Worksheet
Dim ws as Worksheet
For Each ws in Workbook("WorkbookName").Worksheets
ws.AutoFilterMode = False '(can be replaced with .ShowAllData as in example above)
Next
通过特定的Worksheets
通过
Dim ws as Worksheet
For Each ws in Workbook("WorkbookName")
If InStr(1,"Sheet1Sheet2Sheet3Sheet4",ws.Name, 0) > 0 Then
ws.AutoFilterMode = False
EndIf
Next
如果您只使用一个Workbook
,则可以省略Workbook("WorkbookName")
。
如果需要参考执行宏的Workbook
,请将Workbook("WorkbookName")
更改为ThisWorkbook
。
!下面的代码只有在使用 .ShowAllData
!时才能工作。
保存筛选准则的(重置准则之前执行):
Dim ws As Worksheet, i As Long, j As Long, x As Long, arrFCriteria(), Item As Filter
x = ThisWorkbook.Worksheets.Count
ReDim arrFCriteria(1 To x, 1 To 1)
i = 1
For Each ws in ThisWorkbook.Worksheets
j = 1
If ws.FilterMode Then
For Each Item in ws.AutoFilter.Filters
If ws.AutoFilter.Filters.Count > UBound(arrFCriteria, 2) Then ReDim Preserve arrFCriteria(1 To x, 1 To ws.AutoFilter.Filters.Count)
If Item.On Then
arrFCriteria(i,j) = Item.Criteria1
Else
arrFCriteria(i,j) = 0
EndIf
j = j + 1
Next
EndIf
i = i + 1
Next
其中arrFCriteria是具有存储滤波器准则的二维数组。
可以使用索引恢复过滤器( Worksheets()
的一维索引,.AutoFilter.Filters().Criteria1
的索引)。
还原过滤器(最后添加):
For i = LBound(arrFCriteria, 1) To UBound(arrFCriteria, 1)
j = 1
If Not ThisWorkbook.Worksheets(i).AutoFilter Is Nothing Then
For Each Item In ThisWorkbook.Worksheets(i).AutoFilter.Filters
If arrFCriteria(i, j) <> 0 Then
ThisWorkbook.Worksheets(i).AutoFilter.Range.AutoFilter Field:=j, Criteria1:=arrFCriteria(i, j)
End If
j = j + 1
Next
End If
Next i
基本代码如下所示:
Sub MyMacro ()
'Part0 (variable declaration, make sure to include those I wrote)
'Part1 (saving criterias)
'Part2 (loop through worksheets, reset filter)
'Part3 (your macro code)
'Part4 (restoring filters)
End Sub
https://stackoverflow.com/questions/45974723
复制相似问题