2 min readfrom Microsoft Excel | Help & Support with your Formula, Macro, and VBA problems | A Reddit Community

Office Script - Delete rows based on values

function main(workbook: ExcelScript.Workbook) { const currentSheet = workbook.getActiveWorksheet(); const DELETE_Animals = [ "Zebra", "Fish", "Monkey" ]; // Get all values from the sheet const usedRange = currentSheet.getUsedRange(); const values = usedRange.getValues(); console.log(`Starting with ${values.length} rows.`); // Filter out the rows with undesired colors const newValues = values.filter(row => !DELETE_Animals.includes(row[9])); console.log(`Finished filtering. ${newValues.length} rows remaining.`); // Clear the original range usedRange.clear(); // Write the filtered values back to the worksheet starting from the top if (newValues.length > 0) { currentSheet.getRangeByIndexes(0, 0, newValues.length, newValues[0].length).setValues(newValues); } console.log(`Process completed.`); } 

I have an application that exports an Excel file daily. Power Automate grabs the files and runs this script against it. It works great on 9/10 accounts. One account always fails. If I manually run this script on the account that always has problems, it errors out with the following.

Line 25: Range setValues: The argument is invalid or missing or has an incorrect format.

[16, 65] Argument of type 'string | number | boolean' is not assignable to parameter of type 'string'. Type 'number' is not assignable to type 'string'

Beers if you can figure out this one!

submitted by /u/Iowadigger
[link] [comments]

Want to read more?

Check out the full article on the original site

View original article

Tagged with

#rows.com
#financial modeling with spreadsheets
#Excel compatibility
#row zero
#Excel alternatives for data analysis
#Excel alternatives
#no-code spreadsheet solutions
#cloud-based spreadsheet applications
#Office Script
#ExcelScript
#workbook
#currentSheet
#DELETE_Animals
#usedRange
#setValues
#getValues
#filter
#Power Automate
#script
#range