I have a couple of years of data in my Transactions sheet (3387 rows). I noticed a while back that a number of them weren’t included in my filters, i.e. the green box only extended from row 1 to row 3257. I thought it was no big deal, so just turned the filters off, selected all and turned them back on. A couple of days later I noticed the same thing, a few of the rows at the bottom were not filtered.
After a few days of observation, it looks like this is occurring during Tiller’s overnight auto-fill. If three new transactions were loaded overnight, they would be added at the top and the three transactions at the bottom would be pushed down “outside” the filter window.
There’s no real harm here, as there’s no data loss, and I can easily re-apply the filters. Just wondering if this is a Google Sheets issue.
I’m not sure if the select all difference matters, as I cannot seem to reproduce your issue either way.
Have you added any new columns to the Transactions sheet? Any check boxes?
I can reproduce your issue if a column has check boxes. The filter range does not auto-expand with a new row added with data.
The data filter does auto-expand when the check box is not present.
Note that the default Fill will append rows to the Transactions sheet and sort them to the top by descending Date, as opposed to inserting them at the top.
One option would be to automate the data filter reset using a time-driven macro that runs after auto-fill. I do this actually before auto-fill, just to avoid any confusion that can happen when forgetting to clear filtering (and not because of your check box issue). Macros can also be run using a hot-key.
For reference, here’s the macros.gs code.
function resetDataFilter() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName('Transactions');
spreadsheet.setActiveSheet(sheet);
var range = sheet.getDataRange();
var filter = sheet.getFilter();
if (filter) {
filter.remove();
range.createFilter();
}
sheet.getRange('A2').activate();
};
I will say that I’ve seen The Sheet Sanitizer hang while writing the Transactions sheet that contains a cell with a TRUE value (i.e. checked check box). Maybe it’s specific to that tool, but something to keep in mind.
I noticed the same thing a while back, but all I do is turn off the filters, then turn on again without highlighting anything. It will automatically select the current full range.