Page 1 of 1

[Solved] AutoFilter dropdowns back after they are removed

PostPosted: Mon Jan 27, 2014 3:52 pm
by dreadnought
I have a spreadsheet I created with OpenOffice 4.01 that has multiple sheets. Over the last couple days I have used the AutoFilter on some of these sheets. Every time I open my spreadsheet, the AutoFilter dropdowns come back! I go and remove them (I select the header row, select AutoFilter, and they go away), then save my spreadsheet, and open it up again. The AutoFilter dropdowns are back as if they were never removed.

Has anyone experienced this? Any way for me to fix it? One thing that's odd is when I go to AutoFilter after opening my spreadsheet, the AutoFilter icon isn't depressed as if it was on. But when I select it, the AutoFilter dropdowns go away. Until I re-open the spreadsheet anyway, and then they are back.

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Mon Jan 27, 2014 6:57 pm
by Villeroy
Click any single cell which belongs to the filtered list.
Then remove the auto-filter.
And of course we save our office documents in ODF format.

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Mon Jan 27, 2014 8:08 pm
by dreadnought
I just tried that... the AutoFilter icon is not depressed, which seems weird because I'm looking at the AutoFilter dropdowns across my entire header row, and when I click AutoFilter I get ANOTHER row of AutoFilter dropdowns. Calc just won't release the AutoFilter dropdowns from my header row for anything other than my current Calc session.

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Mon Jan 27, 2014 9:13 pm
by Villeroy
Remove all filter ranges from that list if they belong to the sheet in question.

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Sat Apr 26, 2014 7:07 pm
by rrichard63
I am having exactly the same problem as dreadnaught, except that the problem doesn't reappear only when I reopen a spreadsheet after closing it. Neither of the suggestions in this thread work. I have clicked on many single cells and then on Data-->Filter-->Auto Filter. Sometimes the filter disappears but only temporarily. Sometimes it doesn't disappear at all. And when I look under Data-->Define Range, there is nothing there to delete.

One thing that does seem to work is to copy the entire sheet to a new sheet. The filters do not appear to be included in the copy. Time will tell.

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Thu Apr 02, 2015 9:52 pm
by cjesag
Same thing here. The autofilter dropdowns are visible. I go to the Data/Autofilter menu and it is not even selected. I have to select it, which make no change in the spreadsheet because it is already showing the autofilter, and then deselect it. I'm not sure what is causing it to reactivate.

AOO 4.1.1; MacBook Pro; OSX 10.9.5

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Mon Jun 15, 2015 5:35 pm
by Phillip
A suggestion:
As a result of testing for my post:
Filter on part of a column only - ... =9&t=77630
I managed to upset CALC so that it created additional Autofilters that I had trouble deleting.

Apparently each time I created a Autofilter, CALC creates a database range called "_Anonymous_Sheet_DB_nn" in Data>Select Range. I ended up with a number that had no function.

With ref to attached test file:
Autofilter test.ods
(10.7 KiB) Downloaded 153 times

Selected range B7:B19 - "Range has no header, use first cell" - yes : Autofilter dropdown box on cell B7 - ok.
Data>Select Range shows "_Anonymous_Sheet_DB_1".
Deleted by: Reselect range B7:B19, Data>Filter>Autofilter (as it is a toggle)
Autofilter dropdown box disappears from cell B7 -
Data>Select Range showed (no ranges)

If instead of selecting the original range of B7:B19, select range B7:B16
then Data>Filter>Autofilter (as it is a toggle) apparently deletes the autofilter since the Autofilter dropdown box disappears from cell B7 -
BUT Data>Select Range still shows "_Anonymous_Sheet_DB_1".

If you save and then reopen the file - the Autofilter dropdown box re-appears in cell B7

Could this issue result from such rogue "_Anonymous_Sheet_DB_nn"' ?

I know I could do Data>Select Range - then go through each "Anonymous" - and Data>Filter>Autofilter, but if the Autofilter toggle is already off because of above, then you have to:
Data>Select Range - choose
Data>Filter<Autofilter -to toggle ON
then Data> Filter>Autofilter -to toggle off and so delete the "Anonymous x".
Would it not be easier to directly delete these - and be able to name them?

However see attached
Autofilter test2.ods
(10.71 KiB) Downloaded 146 times
- how can I get rid of these "Anonymous" ?

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Mon Jun 15, 2015 5:42 pm
by Phillip
NB. Even deleting the complete column containing the data range in Autofilter test2.ods does not work!

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Mon Jun 15, 2015 7:16 pm
by Villeroy
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

Sub removeAllDBRanges
with ThisComponent.DatabaseRanges
   c = .getCount()
   for i = c -1 to 0 step -1
      db = .getByIndex(i)
      s = db.getName()
   next i
end with
End Sub

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Tue Jun 16, 2015 9:45 am
by Villeroy
Alternatively you can simply turn off all auto-filter buttons instead of removing the db-ranges:
Code: Select all   Expand viewCollapse view
REM  *****  BASIC  *****

Sub removeAllDBRanges
with ThisComponent.DatabaseRanges
   c = .getCount()
   for i = c -1 to 0 step -1
      db = .getByIndex(i)
      db.AutoFilter = False
   next i
end with
End Sub

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Thu Jun 18, 2015 2:09 am
by Phillip
Thank Villeroy:
To get rid of the Anonymous DB ranges and the the Autofilter button I had to:
First run the basic code to delete all AutoFilter buttons - this left the DBRanges
Then run the basic code to delete all the DBRanges.

Running the code to delete the DBRanges first - left the Autofilter buttons
However then running the code to delete the Autofilter buttons did not work (because there were no DBRanges?)

I have now got a spreadsheet clear of all Autofilters.I will be very careful creating new ones.
By the way, selecting a Anonymous DB Ranges highlights the cells, so Data>Filter>Autofilter sometimes will remove the Autofilter and sometimes prompts to add a new one. Repeating the select & Data>Filter>AutoFilter, then appears to work by deleting both the DBRange & the Autofilter button.

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Thu Jun 18, 2015 1:25 pm
by Villeroy
Well, you may switch to LibreOffice which seems to ignore those _anonymous_ data ranges.

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Sat Jun 20, 2015 12:41 pm
by Phillip
Thanks for the advice Villeroy. When I have time I will give LibreOffice a try, although I must say I have enjoyed, if that is the word, the learning curve with OO Calc.

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Wed Jul 01, 2015 3:06 pm
by Phillip
Further to the above:

While Villeroy's code to delete all AutoFilters works (Do delete AutoFilter buttons first, then delete DBRanges). Individual AutoFilters can also be removed.

The problem seems to be that AutoFilter is a toggle. Selecting a cell, or range of cells within a AutoFilter results in another filter being created. What is required is making exactly the same selection as before.

The solution is - When an AutoFilter is created, by either selecting a column or part column, Calc creates a Database Range called "_Anonymous_Sheet_DB_nnn".

To delete the AutoFilter, first selecting the range by "Data>Select range" - choose range, "Ok". This will highlight the column or part column.

Then "Data>Filter>AutoFilter" will remove the filter. (Doing this shows the AutoFilter icon depressed).

Note AutoFilters can be created for multiple columns, or part columns. However only a single "Anonymous" DB range will be created.

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Wed Jul 01, 2015 3:44 pm
by Phillip
Perhaps Dreadnought hit the same bug as I did:
While researching this topic and
Filter on part of a column only:
I managed to corrupt a file and create lots of AutoFilters!

As Remove Filter on "Data>Filter>" is always greyed out, I tried:
Select an "Anonymous" DB Range - then "Data>Filter>Hide AutoFilter"
NOT a good idea, as this removes the "Anonymous" DB Range from "Data>Select Range" - so you can not get it back!
Tried selecting a cell where the AutoFilter was, then "Data>Filter>AutoFilter" - no good, created another AutoFilter.
So deleted this AutoFilter (Data>Select Range, then Data>Filter>AutoFilter)

Selected a range of cells within a column, Data>Filter>AutoFilter created a new AutoFilter
Select a cell within this part column, ""Data>Filter>AutoFilter" - this created filters on every column!

After I thought I had cleaned up, I saved the file - on a later reload, lots of AutoFilters all over the place. Second reload, Calc said file was corrupt and unrecoverable!

Luckily I had a fairly recent backup. Moral: do not test on important files.

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Wed May 11, 2016 10:15 pm
by PeterBinney
Is this ever going to be fixed??

I am on 4.1.2 and the problem is still there

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Wed May 11, 2016 10:37 pm
by Villeroy
Two solutions:
Install LibreOffice (sooner or later you will install it anyway)
Run my above macro code.

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Thu May 12, 2016 12:37 pm
by Phillip
Actually it is a case of Autofilters not being removed correctly.
Further to my Jul 01, 2015 3:06pm post above.
The problem is caused by selecting a different range and then doing Data/Filter/AutoFilter. This appears to get rid of the autofilter as the down arrow in a box is no longer displayed.
However the original range is still recorded by the "_Anonymous_Sheet_DB_n" (Data/Select Range), so when you reload the file, the Autofilter is re-displayed.
Is this a bug or deliberate?
Since you can create autofilters on multiple columns with one selection (creates only a single *Anonymous") and multiple Autofilters on the same column, this seems ok to me. The only fix is, I suggest, that if the selection is different to any defined *Anonymous*, then the Autofilter should not be turned off.
So the solution is:
Make sure you select exactly the same range as the Autofilter.
The simplest way is to select via Data/Select Range - choosing each *Anonymous* in turn until you get the autofilter you want to remove. Using shortcut keys for speed:
Alt+D R <Select range> <Enter> (if correct range then:) Alt+D F F (Switch off autofilter).
NB. I tried LibreOffice did not like at all. OO Calc is :super:

Re: AutoFilter dropdowns keep coming back after they are rem

PostPosted: Mon Apr 27, 2020 4:29 am
by CraigLinNC
FIXED IT! I'm new to Open Office, but skilled in Excel. It's been a fairly simple learning process, but this Zombie Autofilter issue has been a 4-hour research project! I have a good memory for trivia, and so I've worked out a decent solution. And no; none of the many existing suggestions worked, and I don't want to get involved in programming code just to fix a simple issue. Each little tidbit of what did work eventually added up to this (below) solution.

EDIT 4-28-20: I should clarify that this problem is more than just turning on/off Autofilter. We can turn them on or off within a worksheet, within a working session. That's fine. But when we turn them off and then save the file and exit; or, if we Save As an XLS file and email, the Autofilters return! Like the Living Dead!

The problem is turning OFF the autofilters, then Save the file with the filters OFF. Then Exit the worksheet or exit Calc or exit Open Office, and at the next session, opening the file returns the autofilters ON. Likewise, an Excel User opening the file will see Autofilters On in Excel. I want them OFF and to STAY OFF, until I say when they can come back on!

One user solved the problem of deleting the Anonymous database ranges by copying the contents of a worksheet to a new, inserted worksheet. Then deleting the original worksheet and renaming the copy. I did that, and ALL the defined database ranges were deleted! Including the Anonymous ranges! Just like that: They were gone! My conditional formatting, formulas and so forth, all copied into the new worksheet. (I remembered to Paste Special, and include Formats.)

These Anonymous database ranges seem to be created whenever any type of database operation goes on. For me, in the simple world, I just do Sort, Filters, and Named Ranges. But I couldn't find an understandable reason for both Named Ranges and Database Ranges. Now, I can see that data manipulation apparently require the database ranges. I'm supposing formula operations work with Named Ranges.

In the "new," copied worksheet, I began by taking my Named Ranges (used in formulas) and also making them database ranges. I chose to put a lowercase "d" in front of them, to distinguish them from Named Ranges and gave them slightly different names. Example: Location (named range), dLocated (database range). Then I began testing.

First of all, whenever I sorted those columns, used for Lists in Validation, I only had to click anywhere in the list and use the tool button. No need for selecting every time. They'd been Named Ranges, but when I did various sorts without the Database Range, that's when I started getting Anonymous Ranges. Not any more.

Secondly, I created a "d" range for my original master range of information. I included the column headings, just to see. I hadn't been using a Named Range for that, just simply clicking on the Autofilter and then "trying to" turn it off.

From that point forward, I used the worksheet routinely. I could now turn on or off the Autofilter, and it never comes back unless I explicitly leave it on when I save and exit the workbook. I also routinely check the Data >> Define Range, and Data >> Select Range. No more Anonymous ranges. And I periodically check the Navigator, where also there are no more anonymous ranges.

I also learned: If I intend to only autofilter 1 column, I create a Database Range just for that column. If I then click in the column and turn on Autofilter, I get 1 arrow. And I can turn it on or off without any problems.

WHENEVER I insert rows or cells to anything that has a name, I do so from within the existing named Database or Named Range! I also have Tools >> Options >> Open Office Calc >> General >> "Expand references when new columns/rows are inserted" actively checked -- ON.

No more just adding data at the bottom and copying formulas.

It seems that Open Office somehow requires a database range for simple tasks like sorting or filtering. Requires! Really? And no, I don't want to use Libre.

Going forward, at least in my own mind, I expect to create both a database range AND a Named Range for various actions and events. I see that the "d" Database ranges don't show up in the Cell Address drop-down at the top, left corner. They only show up in Navigator >> Database Ranges, or Data >> Define/Select Range.

I also noticed that Anonymous ranges are listed as "Ranges" in Excel, when I saved the workbook file as an .XLS file to email to someone.

Although this is a kind of kludge-type solution, it works. No matter how often I turn on or off the Autofilter, or the Standard Filter, I never get those Anonymous ranges anymore. Those Anonymous ranges apparently are the cause of the problem.

And, to tell the truth, it's a bit faster and easier to do a sort on my lists by simple choosing the dRange-Name. Calc doesn't seem to mix them up at all. I've used similar but not exact names for the same ranges, and Calc knows which is which. Hope this lowers the headache-medication usage in the world a little bit.

Re: [Solved] AutoFilter dropdowns back after they are remove

PostPosted: Mon Apr 27, 2020 5:10 pm
by Phillip
Yes creating a database range before setting autofilter does prevent creation of an "-Anonymous..." data range.
-> Select range, Data>Define Range> enter range name.
-> Data>Select Range>choose range name (highlights range). Then immediately: Data>Filter> Autofilter toggles Filter on/off.
Plus deleting the range name also removes the Autofilter.

BUT if you select only part of a column for the range. say rows 3-20 of col A and give it a name.
Then susequently use the filter - not only does the filter get extended down the column until the first blank cell, then the range name is also extended as well!

Re: [Solved] AutoFilter dropdowns back after they are remove

PostPosted: Tue Apr 28, 2020 4:42 am
by CraigLinNC
Well; this was about removing Zombie Autofilters, which the post accomplishes.
If someone wants to filter part of a column, that's a bit strange really. Why have a column and then filter only part of it? I read the linked post, and the user was building various accounts (bank?) and wanting to sub-sort or something. The simplest way to prevent the range extending is to put an empty row between the accounts. Then you don't have a database; you have a spreadsheet with data in it.

As for selecting a range, turning on and off the filter, and that's a fix. No, it isn't: Not for this problem of keeping them off when exiting the file. Many people have tried that -- and several variations -- and although it works during an open session, the filters come back when the file is closed, then opened at a later time (I edited the original post above). Attempting to delete an anonymous range by selecting it, also never works. Re-selecting an anonymous range and then giving it a name didn't work.

As I said, over 4 hours of reading and searching, not only this forum, demonstrated that nobody put together a workable solution to specifically the problem of autofilters returning without permission.

Bottom line: Outlier unusual utilization of a feature in a major application generally may or may not work. But to turn off autofilters and have them come back, that's not an outlier. Nor is the creation of *mandated* anonymous database ranges. This really ought to be changed in a future release of Calc. Since it's not, we're stuck with a kludge repair.

Re: [Solved] AutoFilter dropdowns back after they are remove

PostPosted: Wed Apr 29, 2020 8:04 pm
by Phillip
As a retired developer I think the problem is not realising why CALC requires a name for the range of cells in an AutoFilter and what happens to a range when an AutoFilter is used.

To create an AutoFilter the user can either:
1: Select a range, name it by Data>Define Range> enter a user-name.
In addition if any other changes are made:
1.1: Data>Select Range> choose existing range.
2: Data>Filter>AutoFilter - sets an AutoFilter on that range.
1A: Select a range,
2: Data>Filter>AutoFilter - in this case CALC creates an "Anonymous" name for the range of cells.
Although I use named ranges elsewhere, for me creating an "anonymous" name saves me unnecessary effort.

To properly remove the AutoFilter, the user must do the reverse:
3: Select the appropriate range by Data>Select Range> select the appropriate name - Defined name or "anonymous" name.
4: Data>Filter>AutoFilter toggle off
Testing this procedure on a copy of my spreadsheet all 13 "anonymous" names were deleted, with none of the AutoFilters coming back on reloading the spreadsheet.

If however the user does:
3A: Select with the mouse the AutoFilter first cell (with the drop selection box in it) and just some of the cells below but NOT the complete data range.
4A: Data>Filter>AutoFilter toggle off
This clears the AutoFilter toggle and removes the drop selection box in the first cell, but does not in fact work. (Why should CALC alter the properties of a different data range?).
Reloading the spreadsheet will resurrect the AutoFilter - the so-callled "zombie" effect.
NOTE this applies to both named and "anonymous" ranges! I suspect this is not realised as the user always does step 3 for named ranges, not 3A.

What further complicates matters is when an AutoFilter is used.
This causes the range to be extended down to just above the first blank row, including any intermediate blank cells. However not to any lower rows containing data.
Again this applies to both named and "anonymous" ranges! Why I don't know - perhaps a bug or the basic design of CALC.

Therefore unless you absolutely know which cells to select, steps 3 & 4 is the only way to remove an AutoFilter drop down.

Once an "anonymous" name has been created defining a user name for the same range of cells, has the effect of making it impossible to delete the "anonymous" name other than by a script.
Much easier to first delete the user name, then delete the "anonymous" name by steps 3& 4.

It is in fact easy to check if AutoFilters for unnamed ranges have been correctly removed - the relevant "Anonymous" name will have been deleted from the Data Range list.
Data>Select Range> select name shows which are still valid.
It is not possible to do this for AutoFilters on named ranges, as of course the name is still in the list - unless also deleted!
Hence AutoFilters on named ranges will be also be resurrected if you use steps 3A on anything other than the correct complete range!

As to the CraiglLinNC's other comments:
I would thought re-creating a complete spreadsheet far more work than steps 3 & 4 which will permanently remove the AutoFilter.
I would agree I do not have a database, I have all my annual spending in a CALC file with extensive analysis on multiple sheets. Why should I use BASE?
Why not a filter on a partial column? Does EXCEL have this limitation?

Re: [Solved] AutoFilter dropdowns back after they are remove

PostPosted: Thu Apr 30, 2020 5:57 am
by CraigLinNC
Member Phillip offers a very useful post here, and it would have been nice to have run into it back when I and others were trying to figure out the anonymous ranges. I now can see why one would first need to remove the named range that's duplicating the anonymous range, then removing the same anonymous range.

Perhaps going forward, this will also help people fix the problem of closing a file then re-opening it only to find the autofilter drop-down arrows have returned. Excellent! Two solutions is better than only one.

Apropos recreating a spreadsheet, that didn't happen. One thing Calc seems quite competent in doing is to carry over the vast majority of information from an original worksheet to a copy (within the same workbook, at least). I simply clicked the corner top left "everything" cell, pressed Ctl-V and went to the newly inserted Worksheet. Then I used Paste Special, and ensured that Formulas and Formats were checked.

When I pasted, everything copied over. Additionally, since I have user-defined row heights, the row heights also changed to my settings. I had essentially a perfect copy of the original Worksheet in the new one. The only thing missing was the anonymous ranges.

I think the combination of knowing how to quickly remove all the anonymous ranges, as well as knowing how to remove them one at a time, is a major solution. Additionally, understanding the interaction between the Database Named Range and the Anonymous range with the same selection offers a "best practices" solution for everyday use of Calc.

As for whether Excel allows easy selection and autofiltering of partial columns, I don't know. It's been a long time since I used Excel. Yes; I can understand why someone might want to filter a partial column. My thought only was that Excel, as a paid software program will offer a lot of capabilities not necessarily available in a free application. We should all remember that Calc is free to use, with no license issues or annual lease payments. We can't expect Calc to exactly replicate every single feature of Excel, I think. The fact that Calc is SO powerful, and also is so closely similar to Excel is a superb gift to those of us who can't afford M$ Office.