OfficeScripts to delete visible rows after filtering

Question

Good Day!!

Have a data dump that comes from one of my systems into excel. The size of the data can vary from 200 rows to 250,000 rows. So to avoid some load restrictions, I have found the best way to manipulate/clean this data is avoid creating a table, and to use Excel's built-in filters, and one by one, filter to make the data to be deleted visible...then delete it. Works perfectly!

However, this issue is, the row counts are always different. When I have true ranges defined, the script runs perfectly (A2:L20000). However, when I attempt to define a usedRange, it gives me an error noting the argument is invalid. I can not figure out how to overcome this.

this errors out on the 10th line selectedSheet.getRange(usedRange).delete(ExcelScript.DeleteShiftDirection.up); which is the same as the 16th line, so I am guessing that will error out also.

Here is the snippet of the code.

Any help or guidance would be appreciated!

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let usedRange = selectedSheet.getUsedRange();
    console.log(usedRange);
    // Toggle auto filter on selectedSheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getRange("A1:L1"));
    // Apply values filter on selectedSheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 10, { filterOn: ExcelScript.FilterOn.values, values: ["0"] });
    // Delete visable on selectedSheet
    selectedSheet.getRange(usedRange).delete(ExcelScript.DeleteShiftDirection.up);
    // Clear auto filter on selectedSheet
    selectedSheet.getAutoFilter().clearCriteria();
    // Apply values filter on selectedSheet
    selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 6, { filterOn: ExcelScript.FilterOn.values, values: ["Expense", "Income"] });
    // Delete range visable on selectedSheet
    selectedSheet.getRange(usedRange).delete(ExcelScript.DeleteShiftDirection.up);
    // Clear auto filter on selectedSheet
    selectedSheet.getAutoFilter().clearCriteria();

Brian Gonzalez · Accepted Answer · 2023-01-13T01:25:55.933

To answer your question, the reason UsedRange doesn't work here is because selectedSheet.getRange() expects a string argument. The string argument is the range's address.

To get around this you could write something like selectedSheet.getRange(usedRange.getAddress()).delete(ExcelScript.DeleteShiftDirection.up); But the deletion won't work with the usedRange() this way.

I added a function I wrote to help with deletion. And I substituted your lines of code where you delete with function calls to this new functions. You can try the code below and see if it works:

    function main(workbook: ExcelScript.Workbook) {
      let selectedSheet = workbook.getActiveWorksheet();
      let usedRange = selectedSheet.getUsedRange();
      console.log(usedRange);
      // Toggle auto filter on selectedSheet
      selectedSheet.getAutoFilter().apply(selectedSheet.getRange("A1:L1"));
      // Apply values filter on selectedSheet
      selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 10, { filterOn: ExcelScript.FilterOn.values, values: ["0"] });
      // Delete visable on selectedSheet
      deleteFilteredRange(workbook)
      // Clear auto filter on selectedSheet
      selectedSheet.getAutoFilter().clearCriteria();
      // Apply values filter on selectedSheet
      selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 6, { filterOn: ExcelScript.FilterOn.values, values: ["Expense", "Income"] });
      // Delete range visable on selectedSheet
      deleteFilteredRange(workbook)
      // Clear auto filter on selectedSheet
      selectedSheet.getAutoFilter().clearCriteria();
    }
      
      function deleteFilteredRange(workbook: ExcelScript.Workbook) {
        let selectedSheet = workbook.getActiveWorksheet();
        let startRow = 2 //Two would be the second row after the header row range.
        let lastRow = selectedSheet.getRange("A1048576").getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1
        selectedSheet.getRange(`${startRow}:${lastRow}`).delete(ExcelScript.DeleteShiftDirection.up)
      }

This script may not work if your dataset is too large however.

OfficeScripts to delete visible rows after filtering

1 Answers1