How do I filter out blank rows in Office Script

Question

In Excel online, I am attempting to create an Excel office script to filter out blank rows/records in a worksheet. To achieve this I would like to filter the A column by removing all of the blank cells.

In the below code I am attempting to find all of the values in the A:A range and write them to an array, before then using them to filter the sheet. However, this is not working. If there is a way to not filter on a generated array and instead filter to exclude nulls in Office Scripts that would be preferable.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    // Apply values filter on selectedSheet
    let FilterArray = selectedSheet.getRange("A:A");
    selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 0, { filterOn: ExcelScript.FilterOn.values, values: [FilterArray] });
}

The current workbook uses the below code to filter the values with VBA.

Sub FilterAllSheets()
   Dim Sheet As Worksheet
   On Error Resume Next
   For Each Sheet In Worksheets
      Sheet.Range("A1").AutoFilter 1, "<>"
   Next
   MsgBox ("All sheets filtered")
End Sub

Thank you very much in advance for your response and guidance.

taller_ExcelHome · Accepted Answer · 2023-07-20T04:47:31.013

You can apply a custom filter to hide blank cells in the column.

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  // Toggle auto filter on selectedSheet
  selectedSheet.getAutoFilter().apply(selectedSheet.getRange("A1"));
  // Apply custom filter on selectedSheet
  selectedSheet.getAutoFilter().apply(selectedSheet.getAutoFilter().getRange(), 0, {
    filterOn: ExcelScript.FilterOn.custom,
    criterion1: '<>'
  });
}

btw, you could get the same result with value filter.

function main(workbook: ExcelScript.Workbook) {
  const sheet = workbook.getActiveWorksheet();
  const usedRange = sheet.getUsedRange();
  const firstColumnValues = sheet.getUsedRange().getColumn(0).getValues();
  // remove header and filter blank
  let filterValues = firstColumnValues.slice(1).filter(x => x != "");
  let strFilterValues: string[] = [];
  // convert to string array
  filterValues.forEach(value => {
    strFilterValues.push(value.toString());
  });
  sheet.getAutoFilter().apply(usedRange, 0, {
    filterOn: ExcelScript.FilterOn.values,
    values: strFilterValues
  });
}

Thank you very much for the specific recommendations. Both of these are exactly what I was looking for. In my use case the first option is what I needed. Good to know I can use the FilterOn.Custom to use a specific criteria. — Impugno, Jul 20 '23 at 14:52

How do I filter out blank rows in Office Script

1 Answers1