Description: Certain blank lines in a sheet cause filtering in filtered columns to stop - any values in the column beyond that line will be displayed, even if they don't match the filter. Steps to Reproduce: 1. Open the attached .ods file and follow the instructions within; namely: 2. Click the autofiltered column header 3. Uncheck all but "VISA" Actual Results: Extraneous rows are present after filtering Expected Results: Only row 2 should be visible after filtering. Reproducible: Always User Profile Reset: No Additional Info: This breaks badly expense tracking in sheets that have total rows (a total row would have a blank value for the filtered "Payment" column from the example). The bug is probably reproducible on all OS-es, and I've tested on Ubuntu.
Created attachment 144970 [details] Example .ods file that breaks filtering
Hello, in this file is set range You can unzip ods file and see it in file content.xml -<table:database-ranges> <table:database-range table:name="__Anonymous_Sheet_DB__0" table:orientation="column" table:display-filter-buttons="true" table:target-range-address="Expenses.A1:Expenses.A5"/> </table:database-ranges>
@rall, I'm not sure I fully understand your comment. Do you expect a regular user (not a developer) to unzip the .ODS and look at content.xml? What exactly do they see there? What does that range mean? Why would the filter stop? What if they encounter this problem in a real-life file, with thousands of rows? I hope you realize that's how I encountered it, and I spent a good hour isolating the problem to a small enough file to make investigation easy. Anyway. Can this bug please be reopened? As long as there is no clear indication to a regular end user, *in the Calc UI* as to why this filter is stopped by a blank line, I don't believe this bug is "RESOLVED" or "NOTABUG" in any way. I'm a developer and I still don't understand what's going on. Let's try to be a little more user-friendly than that.
Hello, I think that's because user set range and then created autofilter. In such case is autofilter's range corresponding selected range.
Just ran into this issue again, and would like to ask for a 3rd party opinion. Thanks!
You can't confirm your own bugs. Moving it back to UNCONFIRMED until someone else confirms it.
Reproducible in: Version: 6.1.4.2 Build-ID: 10(Build:2) CPU-Threads: 4; BS: Linux 4.12; UI-Render: Standard; VCL: gtk3_kde5; Gebietsschema: de-DE (de_DE.UTF-8); Calc: group threaded It is possible to avoid described behavior by marking all involved columns before activating autofilter. Using this as a workaround reveals that data seems to be filtered correctly. BUT in case autofilter is deactivated (by menu or symbol) while values are filtered some rows keep hidden from time to time and must be made visible manually (another bug?).
This happens when new data is added with a blank row below the last data of the filter. So looks fine for me no to extend the filter range, usually there not be blank data row. Doing it with a two columns filter, if data is added below the data of one of them the filter's range it's extended. The filter can be redo by disabling the filter and select the column to do it again. Version: 6.2.0.2 (x64) Build ID: 2ce5217b30a543f7666022df50f0562f82be0cff CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; Locale: es-ES (es_ES); UI-Language: en-US Calc: CL
I've just run into this bug again. I don't know what to do. Unfortunately, raal's comments weren't helpful. Xisco, you mentioned changing the bug status to UNCONFIRMED, but I see it as "WORKSFORME". Since einsachtzehn confirmed reproduction, I hope it's OK that I reopen the bug. If not, please try to reproduce based on my original post.
Still seeing this issue in Version: 6.2.1.2 Build ID: libreoffice-6.2.1.2-snap1
For my own reference when I run into this issue again, the workaround is to re-enable the Autofilter. But please, keep in mind that the VAST majority of users who run into this bug will NOT go to bugs.documentfoundation.org to search for it, and even if they do, they'll search for different terms and won't find this issue, or this particular comment. I really hope this gets fixed at some point.
Created attachment 152358 [details] The spreadsheet with the buggy autofilter I ran into the same problem. See the 3 attached files: a spreadsheet created in Calc and saved in ods and xlsx format, and an image showing 19 additional lines at the bottom when applying this filter. I have more files with the same problem. CALC, including 6.2, created a wrong filter. I did not examine the ods and xlsx file, because in any case, people are not going to manually fix the ods or xlsx files. I add more facts: 1. In my example the wrong filter is being create when I create autofilter only for the last column, "category". In all other cases autofilter works correctly. 2. Out of my curiosity I opened the file with the wrong autofilter in another spreadsheet applications. Gnumeris: as LO Calc. 19 extra rows in both: ods and xlsx files. Excel: in ods file I see 19 extra rows, in xlsx the filtration is correct. No extra rows. Calc in OO 4.1.6 : everything is correct, no extra rows in both: ods and xlsx files. 3. The same behaviour, ( 19 extra rows) occurs when I select this column and apply the standard filter. 4. if some rows are filtered, in order to cancel the autofilter, it is necessary to deselect the autofilter checkbox twice in the menu. It is another bug. 5. When you apply the standard filter,from "more filters" menu Reset filter quickly becomes unavailable. This is another bug. I believe that the bug is confirmed. In addition, I mentioned two more bugs.
Created attachment 152359 [details] the spreadsheet in xlsx format
Created attachment 152360 [details] The image showing 19 additional rows
I compared LO CALC with its predecessor, OpenOffice CALC. OpenOffice does not have this bug, but it also does not create filters for empty cells. Most likely, this bug is the result of improving CALC to add empty cells to filters.
Created attachment 161485 [details] a file with some defined ranges hi, sorry for undigging an old bug ... the OP problem is quite easy, the overall problem very complex: you have to distinguish between - at least - three cases: 1. you did not! define a range / database-range for the area you wish to filter (<data - define range> please do not confuse with 'named ranges'): in this case calc seeks the area to be filtered by itself, 'the smallest rectangle containing the actual focus bordered by 'all empty' cells in columns left and right and rows on top and bottom', sheet borders may substitute empty cells, and defines a "__Anonymous_Sheet_DB__0" name for it, i think that's what happened to the OP, it's sometimes indicated by colouring the background, but hard to spot on large sheets, already confused? single cells are 'expanded' in a different way than selected ranges, single cells in all directions, ranges only downwards? 2. you did! define a range / database-range for the area you wish to filter: then instead of the actual focus the range(s) it touches are taken, and!!! expanded to a 'empty bordered rectangle' as described above, but that's not given a name, 3. you did define a range and! a preexisting "__Anonymous_Sheet_DB__0" range exists: congratulations, the ranges interact, but you have no handle to steer the anonymous one, just to see if it's defined you have to unzip the file, once defined they are never deleted (?), but changed, 4. you have defined several areas: pay attention to which area is the focus, 5. you have defined several overlapping areas: the chaos grows, 6. you have defined several areas and a previous anonymous area exists: it becomes quite hard to get the results you want or to understand what happens, 7. you save the file and recall it: the irregularities are getting more and more 8. you additionally apply 'sort', which follows a different scheme for selecting the area to be processed: an uncontrollable situation, 9. you use sort 'horizontal' (left to right, sort columns): forget anything and start with a new sheet :-( 10. above is how it works mostly, but not in every case ... 11. just try to activate autofilter for cell B5 on the attached sheet, 12. apart from blaspheme: calc does a good job doing many things in the background in a 'semi-intelligent' way so that the users have little work and achieve good results quickly, this works for many simple tasks, but unfortunately in some cases it leads to irritation and in more complex cases to immense difficulties that the developers did not foresee, two sides of the same coin - either take work off the user, or give the user control, or ... irritating 'middle ways' that combine the disadvantages of both sides ...
This is a bug with changes of status - because it's wrong from the beginning. To confirm the behavior doesn't mean to reproduce a bug. Mistake is to start from filtering sheet, when issue is how filter was created. Comments 2, 4, 8 explained that. And workaround was explained, to recreate or redefine filter. Yuri from Comment 12 added a different issue, that cannot be corrected with AUtoFilter off and on. I see it in OO 3.3, contrary to comment 15. But all that is now bug 122975. I close as NotABug for Description. Note that I converted bug 126447 to Documentation, to explain creating filter and empty cells.
@yury reg. c#12: looked into your ods file, as well as @dan in OP you have an "__Anonymous_Sheet_DB__0" range defined in the sheet / the file, this range is affecting the selection for autofilter definitions. The range is not! defined 'on purpose' by you, but likely from former attempts to apply an autofilter to an area which is not defined as a database range. the definition for that range is: <table:database-range table:name="__Anonymous_Sheet_DB__0" table:target-range-address="Sheet1.F1:Sheet1.F363" table:display-filter-buttons="true"> covering cells down to F363, accordingly the rows below stay unfiltered. it may / might be that the 'error' will also show up without that range definition (on a fresh sheet), it looks as if calc uses different 'intelligent algorithms' to decide which range to filter (if you didn't manually do it), depending if you selected / the focus is on a) a single cell, b) a range, c) a full column / full columns, d) a full row / full rows???, e) full sheet, f) multiple sheets???, and each of them split and differentially handeled if anonymous and / or defined ranges are touched by your selection and / or by automatic expansions of that ... very complex, from a short test i'd say in your case: calc says 'full column' is likely too much, i'll take the anonymous range / the part of the column filled with data. observe different behaviour if you manually select F1:F382, @Timur: thanks for the hint with the help / documentation, it is - as often - not very detailed and not really exact, e.g. it does not deal with the different initial situations, and the sentence 'to assign different AutoFilters to different sheets, you must first define a database range on each sheet' is simply wrong, you can easily create auto filters on several sheets without defining ranges there, calc will automa(t/g)ically do it for you, probably it means 'to different ranges / areas of a sheet', there you really have to define ranges first, otherwise the 'automatic' anonymous range is always moved away from one range when a filter is defined somewhere else. All very complex, in the attempt to find a comfortable solution for simple cases of application one has created a confusing mess for more complicated cases ... my attempt to describe the behaviour in c16 is not! fully exact and fully covering, just a hint how complex simple things might grow as each 'special use case' or 'comfort wish' is added as an exception, doubling the complexity for programmers and! users ...
For Pete's sake, how is this still "NOTABUG"? I've run into it again and have to struggle to read through a bunch of cryptic comments to figure out how to unf*ck the file. I know how open source work, I'm not demanding a fix, but please reopen this as a a bug - because it is one.
So here's a workaround in case this bug never gets fixed: 1. Create an auto-filter on another column 2. Create again the filter on the desired column
You need no workaround but use named Autofilter. Remove existing Autofilter. Mark the area to which the filter should be applied. Menu Data > Define Range. Enter Name, Add, OK. Later, in case not already marked, go to Menu Data > Select Range. After the area is marked, enable Autofilter by menu Data > Autofilter.
NotABug - meaning explained that it behaves as it should. Dan, please do not open again. It was multiple times explained why, additional text added after filter was created, Comments 2, 4, 8, 17, 18. So title is wrong "Filter broken by blank line", rather "Rows filled after Autofilter is created are not included". MSO behaves similarly. You found your workaround in Comment 20. But autofilter on another column not needed. Just press Autofilter once, it will expand selection. Nice trick. Or longer, not really needed: press once more tou turn off and turn on autofilter on Column A - that will include "shouldn’t be displayed".
> It was multiple times explained why, additional text added after filter was created That's not quite the only time when the filter stops working. I'll try to make a repro screencast, but basically what happens is this: In that example ODS file I attached, let's say I have a month's log of expense, with several expenses for each day, followed by a blank row, then the next day with several expenses. I go to the row a week back (7 days), set the filter to "VISA", then I edit (MAYBE add) a row that week. Then *SOMETIMES* subsequent expenses are no longer filtered by the "Payment method" column. So I end up with a sheet that's filtered up to a point, then no longer filtered. Being in that situation seems like a bug. I wouldn't know how to end up with a half-filtered sheet even if I wanted to. I never touched Ranges at any point.
Not clear if last comment 23 is the same as comment 0, and if it can be reproduces with the same attachment 144970 [details] or it takes another sample. Feel free to attach another sample with reproducible steps to check that. You need to find what makes them reproducible. Note that I opened a new bug 144048 for range approach.