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

If Sheets/curSheet/.AutoFilterMode = True Then



'Do Nothing



Else



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



End If

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

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