0

I'm using two sheets, one with the main values and reference datas.

The second sheet is for price calculation.

I use =Filter to get the values from sheet one where Sheet1!E2:E <> 1.

How can I get the row from Sheet2 deleted when the value is no longer inside the Filter Condition?

3

Spreadsheet functions cannot delete rows. You need a script for this (introduction to Apps Script). The following function deletes any rows in Sheet2 where the entry in column E is less than 1 or greater than 9.

function deleteRows() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('Sheet2');
  var data = sheet.getDataRange().getValues();
  for (var i = data.length - 1; i >= 1; i--) {
    if (data[i][4] < 1 || data[i][4] > 9) {
      sheet.deleteRow(i + 1);
    }
  }
}  

Notes

  • The indices of array data begin with 0; row and column numbers begin with 1. This is why there is i + 1 in the line with row deletion.
  • The number 4 in data[i][4] corresponds to E, because 0,1,2,3,4... are A,B,C,D,E,...
  • The rows are deleted from bottom up, so that deletion does not move the rows that are yet to be processed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.