Excel Scripts

[nffclincs]

Hi All

Really hoping someone can point me in the right direction, due to moving to office online I am trying to replace old macros in a worksheet with scripts and have become stuck as completely new to this.

I am trying to run a script that in each sheet would delete a row if the value in column CA = 0, is this possible?

 

[Joe4]

Welcome to the Board!

I do not have any knowledge about writing Office Scripts, but you have a few other options at your disposal that do not require any coding.

You could use the old Filtering ability to hide all rows where column CA is 0.

Or, you could use the new FILTER function to create a new list that eliminates all the rows column CA is 0.
See here for details on this great new function: FILTER function - Microsoft Support

 

[nffclincs]

Many thanks for your reply, im getting close with this:
function main(workbook: ExcelScript.Workbook) {

let worksheets = workbook.getWorksheets();
worksheets.forEach(worksheet => {
let usedRange = worksheet.getUsedRange();
// CA is 79th column
let columnCA = usedRange.getColumn(79).getValues();

for (let i = columnCA.length - 1; i >= 0; i--) {
// Check if the value in column CA is 0
if (columnCA[0] === 0) {
// Delete the row if the value is 0
worksheet.getRange(`A${i + 1}`).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up);
}
}
});
}

however getting the error: Line 7: Range getColumn: Parameter out of range

 

[RoryA]

getColumn is 0 indexed I think, so you actually want 78. The error would suggest that CA is the last used column in the worksheet, or your data doesn't start in column A.