Automate deselect blanks in a Filter on a worksheet

coletteanderson 

If you want to automate deselecting blanks in a filter dropdown without explicitly listing the values, you can dynamically retrieve the current filter values and then exclude blanks from the list.

Here is an example of how you can modify your script:

// Get the current filter values
let currentFilterValues = selectedSheet.getAutoFilter().getColumnFilter(11).getDistinctValues();

// Remove blanks from the filter values
let filteredValues = currentFilterValues.filter(value => value !== "");

// Apply the updated filter
selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 11, { filterOn: ExcelScript.FilterOn.values, values: filteredValues });

In this example:

1. getDistinctValues() retrieves the distinct values currently present in the filtered column.
2. filter(value => value !== "") removes any blank values from the list.
3. Finally, the updated non-blank values are applied as the filter.

This way, the script dynamically adjusts to the current dataset, excluding blanks without explicitly specifying other values.

Additional Informations: Office Scripts samples and scenarios

NOTE: My knowledge of the topic is limited, but since no one has answered yet, even though it has been read many times, I posted the question in various AIs and found the above suggested solution for you. The proposed solution is untested. Maybe it will help you further in your project, if not please just ignore it.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark as best response and Like it!

This will help all forum participants.