Excel VBA 如果不存在,则添加自动滤清器

如何检查范围内是否存在自动过滤器,并将其应用。

目前我只是使用


Range/"A1:N1"/.AutoFilter


但是,如果此范围内已过滤,则会禁用它们。

我正在寻找这个,发现了很多用于清洁和重置自动变频器的解决方案,而不是实际检查过滤器是否真正适用的单一的解决方案。
已邀请:

帅驴

赞同来自:

您当前的解决方案应该正常工作,但您可以使用 If statement, 例如


If Sheets/curSheet/.AutoFilterMode = True Then

'Do Nothing

Else

Sheets/curSheet/.Range/"A1"/.AutoFilter

End If

风见雨下

赞同来自:

我刚关闭而不是检查 AutoFilter 在重新使用之前。


Sheets/curSheet/.AutoFilterMode = False
Range/"A1:N1"/.AutoFilter

石油百科

赞同来自:

以下是一个简短的解决方案,其中仅包含自动筛选器,仅当尚未安装时


If Not Sheets/curSheet/.AutoFilterMode Then Range/"A1:N1"/.AutoFilter


优点:只有当场没有自动过滤器时发生的事情

缺点:仅在代码指定自动滤波器时使用,因为它可能发生在用户在另一个范围内安装过滤器。

三叔

赞同来自:

或者在一行中进行:


If Worksheets/"Sheet1"/.AutoFilterMode = False Then Range/"a1"/.AutoFilter

帅驴

赞同来自:

U


sing the if false method ... leaves the filter and header validations untouched...

DoFixValid "ShellManycl", "g4:r4"
'puts as headings 'validation drop downs for the Get of the class


Private Sub CommandButton2_Click//
Dim Ra As Range
Application.ScreenUpdating = False
Set Ra = Range/"f5"/.CurrentRegion
Ra/2, 2/.Resize/Ra.Rows.Count, Ra.Columns.Count/.Clear
' clear all except filter and validation Headings
URaAdd = ActiveSheet.UsedRange.Address ' tidy used range
[j1] = Timer
DoRaShell Range/"F5"/ ' ' get the data below headings
[j2] = Timer - [j1]
Application.ScreenUpdating = True
Set Ra = Range/"f5"/.CurrentRegion
If Not AutoFilterMode Then Ra.AutoFilter n
'not touch filter values of heanings

End Sub

小姐请别说爱

赞同来自:

' Maybe some may be interested in adding validation to a range

'or to get the Public Property Gets / no param / from a class module
'or both from a module


' needs reference to Microsoft VBE extensibility pack

Option Explicit: Option Compare Text
Public VRa As Range, VFormula$, VTitle$, VMsG$



Sub DoFixValid/ClassName$, RaAdd$/
FixGetValidation ClassName
ValidateForRange Range/RaAdd/, VFormula

End Sub

Sub FixGetValidation/ComponentName$/

Dim Li%, PP%, EL%, LineStr$, PosGet&, PA
VFormula = ""
With ActiveWorkbook.VBProject.VBComponents/ComponentName/.CodeModule

For Li = .CountOfDeclarationLines To .CountOfLines
LineStr = .Lines/Li, 1/
PosGet = InStr/LineStr, "rty Get "/
If PosGet > 2 Then
If InStr/LineStr, "Private"/ = 0 Then
LineStr = Mid/LineStr, PosGet + 8/
LineStr = Left/LineStr, InStr/LineStr, "/"/ - 1/
If InStr/"!@#$%&", Right/LineStr, 1// Then LineStr = Left/LineStr, Len/LineStr/ - 1/
VFormula = VFormula & "," & LineStr
End If
End If
Next Li
End With
End Sub

Sub ValidateForRange/Ra As Range, ValidFormula$, _
Optional Title$ = "For List columns ", Optional MsG$ = " Select from drop down list"/
Ra.Select
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=ValidFormula
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = Title
.ErrorTitle = Title
.InputMessage = MsG
' .ErrorMessage = ValidFormula
.ShowInput = True
.ShowError = True
End With
End Sub

要回复问题请先登录注册