AutoFilter dropdowns keep coming back after they are removed

Discuss the spreadsheet application

AutoFilter dropdowns keep coming back after they are removed

Postby dreadnought » Mon Jan 27, 2014 3:52 pm

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.
OpenOffice 4.01 on Windows 7
dreadnought
 
Posts: 3
Joined: Mon Jan 27, 2014 3:42 pm

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Villeroy » Mon Jan 27, 2014 6:57 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: AutoFilter dropdowns keep coming back after they are rem

Postby dreadnought » Mon Jan 27, 2014 8:08 pm

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.
OpenOffice 4.01 on Windows 7
dreadnought
 
Posts: 3
Joined: Mon Jan 27, 2014 3:42 pm

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Villeroy » Mon Jan 27, 2014 9:13 pm

menu:Data>Define...
Remove all filter ranges from that list if they belong to the sheet in question.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: AutoFilter dropdowns keep coming back after they are rem

Postby rrichard63 » Sat Apr 26, 2014 7:07 pm

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.
--
Bob Richard
OpenOffice 4.0.1 on Windows 7
rrichard63
 
Posts: 1
Joined: Sat Apr 26, 2014 6:55 pm

Re: AutoFilter dropdowns keep coming back after they are rem

Postby cjesag » Thu Apr 02, 2015 9:52 pm

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
cjesag
 
Posts: 7
Joined: Tue Oct 18, 2011 3:31 am

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Phillip » Mon Jun 15, 2015 5:35 pm

A suggestion:
As a result of testing for my post:
Filter on part of a column only - https://forum.openoffice.org/en/forum/v ... =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 121 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)

However:
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 119 times
- how can I get rid of these "Anonymous" ?
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 73
Joined: Wed Jan 09, 2013 1:50 pm

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Phillip » Mon Jun 15, 2015 5:42 pm

NB. Even deleting the complete column containing the data range in Autofilter test2.ods does not work!
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 73
Joined: Wed Jan 09, 2013 1:50 pm

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Villeroy » Mon Jun 15, 2015 7:16 pm

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()
      .removeByName(s)
   next i
end with
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Villeroy » Tue Jun 16, 2015 9:45 am

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
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Phillip » Thu Jun 18, 2015 2:09 am

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.
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 73
Joined: Wed Jan 09, 2013 1:50 pm

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Villeroy » Thu Jun 18, 2015 1:25 pm

Well, you may switch to LibreOffice which seems to ignore those _anonymous_ data ranges.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Phillip » Sat Jun 20, 2015 12:41 pm

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.
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 73
Joined: Wed Jan 09, 2013 1:50 pm

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Phillip » Wed Jul 01, 2015 3:06 pm

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.
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 73
Joined: Wed Jan 09, 2013 1:50 pm

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Phillip » Wed Jul 01, 2015 3:44 pm

WARNING - DO NOT TRY THIS ON A FILE YOU WANT TO KEEP
Perhaps Dreadnought hit the same bug as I did:
While researching this topic and
Filter on part of a column only: https://forum.openoffice.org/en/forum/viewtopic.php?f=9&t=77630
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.
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 73
Joined: Wed Jan 09, 2013 1:50 pm

Re: AutoFilter dropdowns keep coming back after they are rem

Postby PeterBinney » Wed May 11, 2016 10:15 pm

Is this ever going to be fixed??

I am on 4.1.2 and the problem is still there
OpenOffice on Windows
PeterBinney
 
Posts: 2
Joined: Sun Nov 06, 2011 5:29 pm

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Villeroy » Wed May 11, 2016 10:37 pm

Two solutions:
Install LibreOffice (sooner or later you will install it anyway)
Run my above macro code.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26129
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: AutoFilter dropdowns keep coming back after they are rem

Postby Phillip » Thu May 12, 2016 12:37 pm

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:
Open Office 4.1.5, Windows 7 Professional 64-bit
Phillip
 
Posts: 73
Joined: Wed Jan 09, 2013 1:50 pm


Return to Calc

Who is online

Users browsing this forum: No registered users and 6 guests