Results 1 to 13 of 13
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Autofilter (2003)

    Is there any way to apply autofilter only to certain selected noncontiguous columns in a worksheet?
    Thanks in advance.

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

    Re: Autofilter (2003)

    I'm not sure what you want to accomplish. AutoFilter works by hiding entire rows.

  3. #3
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autofilter (2003)

    Yes, I know. What I am trying to accomplish is setting autofilter only to, say, columns A, C, D, F G, J, as opposed to all of the columns A-J.

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

    Re: Autofilter (2003)

    But why? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Autofilter (2003)

    I doubt that is possible. Even if you try and do the worst thing and split a list with empty columns to try nd activate autofilter across the appropriate columns...it is not possible. The best solution is to assign the autofilter to the list and simply use the appropriate drop downs where needed. Else revert to an advanced filter to filter non contiguous columns based on criteria ranges.
    Regards,
    Rudi

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

    Re: Autofilter (2003)

    You can only apply AutoFilter to a contiguous list, and there can be only one AutoFiltered range on a worksheet.
    But you can delete the AutoFilter dropdown button from one or more of the column headings (field names) using VBA.
    If there are no (other) shape objects on the sheet, the filter button in B1 can be deleted by the instruction

    ActiveSheet.Shapes(2).Delete

    If there are other shapes on the sheet, you'll have to experiment to find out the correct index number.
    When you have deleted an AutoFilter button, the only way to get it back is to turn AutoFilter off, then on again.

  7. #7
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autofilter (2003)

    Thanks, Hans. Would I just enter that into a module in my Personal.xls file?

  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 (2003)

    You could add the line in a sub in Personal.xls or in the workbook itself, whatever works better for you...

    Steve

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autofilter (2003)

    Thanks, Steve. I think I prefer the Personal.xls route. Could you guide me along?
    Thanks, again.

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

    Re: Autofilter (2003)

    See Legare Coleman's <!post=Personal.xls Tutorial (All),118382>Personal.xls Tutorial (All)<!/post> .

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autofilter (2003)

    Thanks, Hans.
    Here is what I entered into a module in my Personal.xls file:

    Sub Macro(AutoFilter)
    ActiveSheet.Shapes(2).Delete
    End Sub

    However, it is not showing up in my macros. Any advice?
    Thanks again.

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

    Re: Autofilter (2003)

    1) A macro is a procedure (i.e. a Sub) without any arguments after the name, for example

    Sub MyProc()

    If the procedure has arguments, it will not show up in the list of macros.

    2) You should choose a name that does not conflict with built-in names. AutoFilter is a defined name in Excel VBA, so you should avoid it. You could name your macro DeleteFilterButton:

    Sub DeleteFilterButton()

    3) This macro is dangerous - it will always delete the 2nd shape on the active sheet, whatever that shape is. It is not necessarily the 2nd autofilter button.

  13. #13
    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 (2003)

    It may be safer to do it using Visibledropdown property of the of the object instead of deleting the shape. This method can be used to switch the arrows on and off without turning autofilter off and back on. It dows nothing if autofilter is not active...

    Steve

    <pre>Option Explicit
    Sub HideCol2FilterArrow()
    'hides column 2 autofilter arrow
    Dim rng As Range
    Dim i As Integer
    i = Cells(1, 1).End(xlToRight).Column
    Application.ScreenUpdating = False
    If ActiveSheet.AutoFilterMode Then
    For Each rng In Range(Cells(1, 1), Cells(1, i))
    With rng
    .AutoFilter Field:=.Column, _
    Visibledropdown:=(.Column <> 2)
    End With
    Next
    End If
    Set rng = Nothing
    Application.ScreenUpdating = True
    End Sub</pre>


Posting Permissions

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