Strange Autofilter behaviour

[redart]

Hi,

Not sure if this is by design - but the Calc Autofilter function seems to treat cells with a background color as if they have text / data.

My spreadsheet has a contiguous range of (mostly) non-blank cells which I want to autofilter. Below this range there is a single blank row, and below that there is another range containing various results that I do not want autofiltered.

So long as the blank row is truly blank then the first range gets autofiltered and the second does not. However if I change the background color of even a single cell in the blank row, then the second range gets autofiltered along with the first.

This behaviour continues even after I have removed the background color in the blank row. In order to get it back to filtering the first range but not the second, I have to remove the autofilter and then re-apply it.

It seems like the last part could be a a bug, but what about the first?. Thanks.

Version: 7.6.5.2 (X86_64) / LibreOffice Community
Build ID: 60(Build:2) / Linux

[sokol92]

Hello!
Let’s check it together.
In the attached file, select the range A1:B1 and apply an autofilter.
I have numbers from 1 to 5 in the first drop-down list.
TestFilter.ods (8.9 KB)

[redart]

Hi Vladimir,

Thanks for the reply. I get the same result with your test file…

First capture with the yellow background and filtered for “3”…shows the 3 but not 6, 7 & 8. Second capture after clearing the yellow background and resetting the autofilter…results as expected.

[EarnestAl]

You might not have noticed it the test file that the number format for cell A7 is set to FFD320 (orange) so I guess it is a result of
Bug 144549 - Filter by Color does not work for colors, which are set by number format
Fix listed in LibreOffice 7.6 Community: Release Notes - The Document Foundation Wiki

I would expect that if colour is capable of being filtered then it would also extend the range of contiguous cells to be filtered. The only strange thing I see is that in LO 7.2 background colours didn’t extend the range, Release Notes 7.2, AutoFilter improvements

[sokol92]

I updated my version from 7.6.4.1 to 7.6.5.2.
The behavior has changed: previously, when setting an autofilter, the drop-down list showed the numbers 1-5, now 1-8.
I think this is a consequence of the fixed bug tdf#158440 “Filter for background should take colors in empty cells”.


Another thing is to investigate whether the rules are accurately described according to which, when setting an autofilter, the current selection is expanded to the area included in the autofilter.

[mariosv]

I think the best is Menu/Data/Define Range before the Autofilter.
Create the Autofilter while in a cell of defined range.
The filter is saved with the range, and the range is not broken for empty cells.

[redart]

Thanks Miguel. I had tried defining the range but it didn’t seem to make a difference.
I just tried it again on Vladimir’s test file and still no joy. In fact the defined range keeps resetting itself to A1:B10 after I set it to A1:B6.