•2 min read•from 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!
[link] [comments]
Want to read more?
Check out the full article on the original site
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