Results 1 to 9 of 9

Thread: Autofilter (xp)

  1. #1
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofilter (xp)

    I am trying to test,in code, if the active worksheet has Autofilter in use and if so to turn it off. I am unable to get the syntax right. Any takers?
    Thanks.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autofilter (xp)

    Use the AutoFilterMode property of the worksheet:

    If ActiveSheet.AutoFilterMode = True Then
    ActiveSheet.AutoFilterMode = False
    End If

    Note: you cannot set AutoFilterMode to True, instead set AutoFilter for the appropriate range:

    ActiveSheet.Range("A1").AutoFilter

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Autofilter (xp)

    A great site for this info (as well as other info) is MS MVP Debra Dalgleish's Excel Filters -- AutoFilter Programming

    Steve

  4. #4
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter (xp)

    Thanks Hans! Just what I needed. How would I set CurrentRegion of the Autofiltered list as the Print Area and in landscape?
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Autofilter (xp)

    Try this:

    With ActiveSheet.PageSetup
    .PrintArea = Range("A1").CurrentRegion.Address
    .Orientation = xlLandscape
    End With

  6. #6
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter (xp)

    <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  7. #7
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter (xp)

    Many thanks, Steve for the link. However, I'm trying to use modified code to hide the dropdown arrows. It certainly does that but the list is no longer filtered. Can you check the code and see where I'm going wrong?

    Sub FiltList11app()
    MyGroup = List.ComboBox7.Value
    Worksheets("2005Applied").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:=MyGroup
    ' Dim c As Range
    'Dim i As Integer
    'i = Cells(1, 1).End(xlToRight).Column
    'Application.ScreenUpdating = False

    'For Each c In Range(Cells(1, 1), Cells(1, i))
    ' If c.Column > 0 Then
    ' c.AutoFilter Field:=c.Column, _
    ' Visibledropdown:=False
    ' End If
    'Next
    Range("A1").Select
    End Sub

    The apostrophes indicate the code I am using.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Autofilter (xp)

    Try to leave one of the filters with an arrow. Removing the arrow removes the ability to filter on the column.

    Since you remove them all, you have removed the ability to manual filter.

    The example used:
    <pre>If c.Column <> 2 Then </pre>


    to allow filtering on the second column. You must have 1 column to allow filtering on.

    Steve

  9. #9
    4 Star Lounger
    Join Date
    Sep 2002
    Location
    Stafford, Staffordshire, England
    Posts
    585
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofilter (xp)

    Thanks Steve. It's now working perfectly.
    <font color=blue><font face="Script MT Bold"><big>Rob</big></font face=script></font color=blue>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •