CODEWITHSUNDEEP
excelexcel-2010vba
Tim Wilkinson
Tim Wilkinson

Reputation: 3791

Excel VBA add Autofilter if it doesn't exist

How do I check if a range already has autofilters, and apply them if not.

At the moment I am simply using

Range("A1:N1").AutoFilter

However if that range already has Filters on it turns them off.

I have searched for this and found lots of solutions to clearing and reseting autofilters, but none about actually checking if filters are actually applied.

Upvotes: 8

Views: 46405

Answers (6)

Harry S
Harry S

Reputation: 511

' 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

Upvotes: -2

Harry S
Harry S

Reputation: 511

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

Upvotes: 0

Dragonary
Dragonary

Reputation: 11

Or do it in one line:

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

Upvotes: 1

Axel
Axel

Reputation: 2725

Here ist a short solution that turns on the autofilter only if it is not already in place

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

Pros: something happens only if there is no autofilter in place

Cons: Should only be used when autofilters is set by code, since it could happen that a user has set a filter in a different range.

Upvotes: 1

BerticusMaximus
BerticusMaximus

Reputation: 725

Your current solution should work fine but you could use an If statement like

If Sheets(curSheet).AutoFilterMode = True Then

'Do Nothing

Else

Sheets(curSheet).Range("A1").AutoFilter

End If

Upvotes: 9

Tim Wilkinson
Tim Wilkinson

Reputation: 3791

Rather than checking I just turned off AutoFilter before reapply.

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

Upvotes: 6

Related Questions