Excel VBA dodaje autofiltr, jeśli nie istnieje

If Sheets(curSheet).AutoFilterMode = True Then'Do NothingElseSheets(curSheet).Range("A1").AutoFilterEnd 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 heaningsEnd 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 packOption Explicit: Option Compare Text

Public VRa As Range, VFormula$, VTitle$, VMsG$Sub DoFixValid(ClassName$, RaAdd$)

 FixGetValidation ClassName

 ValidateForRange Range(RaAdd), VFormulaEnd SubSub 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 SubSub 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