Autofilter Shows but is turned off

[TukaranXL]

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!!

 

[Andrew Poulsom]

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.

 

[TukaranXL]

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

 

[Andrew Poulsom]

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.

 

[TukaranXL]

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?

 

[Andrew Poulsom]

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

 

[TukaranXL]

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.

 

[Andrew Poulsom]

Do you have any macros in that workbook? Is the worksheet protected?

 

[TukaranXL]

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!!

 

[Andrew Poulsom]

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