I have tried to make an Office Script that loops through each row and finds the one with the text "Total" in column A and then deletes that row. I have not managed to make it work, does anyone know how to change it?
function main(workbook: ExcelScript.Workbook) {
//getting the used range in the worksheet
let usedRange = workbook.getActiveWorksheet().getUsedRange();
//getting the row count of the used range
let lastRow = usedRange.getRowCount();
usedRange = workbook.getActiveWorksheet().getRangeByIndexes(0, 0, (usedRange.getRowIndex() + lastRow), 19)
//getting the values of the range
let values = usedRange.getValues();
//getting the row count of the range
let rowCount = usedRange.getRowCount();
//for every row starting at the last row, checking if the cell in column 'A' contains text "Total". If it is, then delete the entire row.
for (let i = rowCount - 1; i >= 0; i--) {
if (values[i][18] == "Total") {
usedRange.getCell(i, 1).getEntireRow().delete(ExcelScript.DeleteShiftDirection.up)
}
}
}
I think your problem is on this line:
if (values[i][18] == "Total") {
The first array [i] takes the argument which corresponds to the row in the Excel worksheet. The second array [18] takes the argument which corresponds to the column in the Excel worksheet. Since arrays are zero based, the first column, Column A, would correspond to index zero. So you just have to update the value from 18 to 0. And it should perform this check on column A. So the updated code will look like this:
if (values[i][0] == "Total") {