Original AutoFilter range remains valid, although new AutoFilter range is set. Need to copy and paste the data to a new spreadsheet to avoid the issue.
Steps to Reproduce:
1. Open a xlsx file that includes autofilters (in my case, answers from a MS Forms in Calc)
2. Hide AutoFilter
3. Add some columns to the right (D:F in the sample file)
4. Add AutoFilter
5. Apply AutoFilter
6. Hide AutoFilter
Steps 4, 5 and 6 do not take in account the new columns if done while cursor is in the original columns.
Steps 4, 5 and 6 works if done while cursor is in the new columns.
When hidding AutoFilter range must be automatically reseted.
User Profile Reset: No
OpenGL enabled: Yes
Version: 18.104.22.168 (x86)
Build ID: 639b8ac485750d5696d7590a72ef1b496725cfb5
CPU threads: 4; OS: Windows 6.1 Service Pack 1 Build 7601; UI render: default; VCL: win;
Locale: es-MX (es_MX); UI-Language: en-US
Created attachment 167888 [details]
Sample .ods file
3 columns are inheritd from the .xlsx file, and 3 columns are new.
I can confirm with the attached ODS, but not with an ODS I saved from an autofiltered XLSX as described in the steps. Can you share the XLSX as well?
What's odd to me is that the ODS is lacking an _xlnm._FilterDatabase named range that comes via the XLSX autofilter import (see eg. bug 118592 and bug 127301).
Created attachment 167902 [details]
xlsx file without sensible data
The downloaded .xlsx file was edited with LO and saved.
Confirming with the XLSX file in:
Version: 22.214.171.124.alpha0+ (x64)
Build ID: 796c7f612603490dda9277ced0f6ab3cce3bc116
CPU threads: 4; OS: Windows 6.3 Build 9600; UI render: default; VCL: win
Locale: en-US (hu_HU); UI: en-US
When row 1 is selected or the cursor is placed in a cell in the A-C columns (and even D too - strange), the problem is reproducible.
Works fine of the cursor is in a cell of E-F (etc...) columns.
When columns A-F are selected, all of them gets the new autofilter. But then selecting row 1 and turning autofilter off and on makes it appear only in the newly added D-F column - buggy behavior is inverted.
Also happens in older versions: 6.0, 5.0, 4.0, 3.5 - not a new regression.
Created attachment 168031 [details]
Table in original file side by side in Excel and Calc
This A1:C61 range is formatted as a table in Excel, with the Filter button option enabled.
Calc has the Database range feature similar to this, but the filter button option is missing from the UI.
Playing with this in Excel as well, I see:
- In Excel you can either turn on/off the autofilter for the table or the new data outside of the table, if the selection of cells/rows/columns is inside or outside of the table.
If you select a range that contains cells (rows/columns) in both the table and outside of it, the autofilter button is disabled.
- In Calc if the selection is inside the table (Database Range), the autofilter affects only the table.
* This is true if the selection is only partially inside the table like C2:D2 (Excel does not allow autofilter in this case, Calc shouldn't either).
* If the selection is one cell and it's outside the table and not in Column D), then the autofilter is turned on / off for all columns. (this works as if there was no table, but should consider whether it's inside the table or outside and affect only the table or the columns outside of it)
* If the selection is at least partially in Column D, it turns on/off the autofilter in the table. (wrong, autofilter should be disabled)
* If the selection is at least two cells in columns E-F then the selected range has the autofilter turned on (this works correctly, same as if there was no table).
If I'm not wrong, when calc has a filter on a database defined range, it works connected with that range, and also keeps for that range the options for sort.
Seems when it comes from xlsx a database range is created, so the filter it's connected to it, and no add new columns to the filter.