Transaction Sheet Filters Don't "Expand" After Fill

Google Sheets
1

Hi All!

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.

2

I’m curious, how do you select all?

I typically click the box left of column A and above row 1 for full sheet data filters.
image

This selects all rows and columns - notice the Name box is all rows 1:2606 without columns and how rows and columns are highlighted.
image

Compare that to using Ctrl+A to select all cells - the Name box now includes columns A1:W2606 and the row and column highlight color is different.
image

3

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.
image

The data filter does auto-expand when the check box is not present.
image

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.

4

I select all in both ways you describe…just whatever seems easiest at that particular moment.

I have added a column with a check box that I use to reconcile transactions, so it sounds as though that’s it.

Any thoughts as to why that might be the case? I suppose I could change it to something other than that (Just a text “X”)

5

I’m not sure why the check box throws things off :man_shrugging:

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.

6

Thanks! That actually helps with another annoyance…the lack of a “reset all filters” command.

1 Like
7

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.

1 Like