Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Autofilter Shows but is turned off

This is a discussion on Autofilter Shows but is turned off within the Excel Questions forums, part of the Question Forums category; Hi everyone. Been viewing the MrExcel boards for some time now and have always been able to find an answer ...

  1. #1
    New Member
    Join Date
    May 2011
    Posts
    15

    Angry Autofilter Shows but is turned off

    Hi everyone.

    Been viewing the MrExcel boards for some time now and have always been able to find an answer to what I'm looking for but this time I'm completely stumped. I've searched the forum and googled it but can not find an answer or anyone else experiencing the same problem.

    Using Excel 2003 (SP2) and Windows XP (SP3)

    I have a spreadsheet with data in A1:DK15208. The sheet has panes frozen at E13 and an autofilter on the whole of row 12 (A:DK). This spreadsheet is used as a template which is populated with data from other sources via VBA and produces an output report. A while ago I ran one of the reports but when I opened it up the autofilters did not appear to be working although the drop down arrows were being displayed. I went into the template and the same thing was happening - the autofilter drop down arrows are all showing on row 12 but none of them work and in Data>Filter the Autofilter option is available. If I select row 12 then turn autofilter on then it works fine, but when I turn it off again the drop down arrows are still there. If I select another region (say row 10) and put the filter on then another set of autofilter arrows appear and BOTH the autofilters start working.

    This is affecting all the autofilters on each sheet in the workbook so I'm thinking it's maybe a global setting/issue somewhere?

    If I select row 12 and turn autofilter on then save the file, the next time I open it the filter arrows are still showing but have stopped working and the autofilter option is unticked again.

    This has also happened on a number of other spreadsheets we use and I have gone through all the code (workbook, sheet and module) and there is nothing I can see which would cause this behaviour. I wrote the code myself and the file is read-only so no-one else could have messed with the file. I tried copying the sheets to a new workbook but the problem persists.

    Any ideas on what is causing this or how I can stop it?

    Thanks in advance!!

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,594

    Default Re: Autofilter Shows but is turned off

    Welcome to MrExcel.

    It sounds like you have converted your data into a List (Data|List|Create List). When you do this, filter dropdowns automatically appear when the list is selected. To remove it select any cell in the List and choose Data|List|Convert to Range.
    Microsoft MVP - Excel

  3. #3
    New Member
    Join Date
    May 2011
    Posts
    15

    Default Re: Autofilter Shows but is turned off

    Thanks for the quick reply!

    I have gone into Data>List but the Convert to Range option is greyed out. The only selectable options are 'Create List' and 'Hide Border of Inactive Lists'. I've tried clicking in various cells and also selecting all cells but only those 2 options remain available. Is there a way of locating where any of these lists may exist?

    I have a few named ranges in parts of the sheet - could that affect it or is that a different thing entirely?

    Cheers

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,594

    Default Re: Autofilter Shows but is turned off

    If you select a cell with a dropdown do you get the option to Convert to Range when you select Data|List? A List is the only way you can have more than one filter on a worksheet.
    Microsoft MVP - Excel

  5. #5
    New Member
    Join Date
    May 2011
    Posts
    15

    Default Re: Autofilter Shows but is turned off

    No, it's still greyed out then too.

    If I select row 12 (which has the 'ghost' autofilter on it) and select Create List, then click OK it inserts a new row with Column1 to Column256 in the cells and pushes the 'ghost filter' row down to row 13 and displays the List toolbar, rows 12 and 13 then both have an autofilter showing. Rows 12 adn 13 get a blue border around them. When I select a cell not in rows 12 or 13 the new autofilter on row 12 disappears but the other one still shows.

    Once I've created this list I do then get the option to convert to range, but when I do it just removes the blue border and the new autofilter arrows, the original ones stay where they are and continue to be non-functioning.

    I've tried copying to a new workbook again but I still get this non-functioning filter.

    Am I going insane?

  6. #6
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,594

    Default Re: Autofilter Shows but is turned off

    Are the dropdown arrows always visible? It's not Data Validation a a ComboBox is it?
    Microsoft MVP - Excel

  7. #7
    New Member
    Join Date
    May 2011
    Posts
    15

    Default Re: Autofilter Shows but is turned off

    Yes they are always visible no matter where on the sheet I select but no there is no validation on the cells.

    Forgot to mention also that I tried deleting the entire row which got rid of the arrows. I then inserted a new row and re-did the headers, turned the autofilter back on (which at this point worked as a filter), saved and closed. Then I re-opened the file and the autofilter arrows were still there but again they were non-functioning and in Data>Filter the Autofilter option was unticked.

    If I was at home I would just re-install Excel but as I'm at work I'm not able to. I did run detect and repair but that didn't do anything either.

  8. #8
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,594

    Default Re: Autofilter Shows but is turned off

    Do you have any macros in that workbook? Is the worksheet protected?
    Microsoft MVP - Excel

  9. #9
    New Member
    Join Date
    May 2011
    Posts
    15

    Default Re: Autofilter Shows but is turned off

    The file itself is password protected (write-reserved) but none of the sheets in it or the workbook itself are protected.

    There are macros in the file but none that reference the autofilter. This particular file is opened read-only by another file and the macros in that file manipulate the autofilter in this one, but once it's finished it closes this file down without saving changes.

    Thanks for trying to get to the bottom of this by the way, it is much appreciated!!

  10. #10
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,594

    Default Re: Autofilter Shows but is turned off

    Are you able to post the workbook to a share so that we can have a look?
    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com