How do I delete all rows after the last row that has content in a range-CodePudding

I have a sheet where I need to delete all the rows below where I am finished with data entry. The problem is I have checkboxes in one column that run the whole length of the sheet so simply deleting all of the blank rows doesn't work.

Here is a screenshot of an example. In this instance, I would want all the rows below row 15 to be deleted, but the checkboxes in Col N prevent the following script from working for me. Thanks a ton for any feedback!

function removeEmptyRows(){
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PB").getRange("A13:H");
  var maxRows = sh.getMaxRows(); 
  var lastRow = sh.getLastRow();
  sh.deleteRows(lastRow 1, maxRows-lastRow);
}

CodePudding user response：

I believe your goal is as follows.

From I would want all the rows below row 15 to be deleted and your showing image, you want to delete rows from row 15 to the bottom row of all columns.

In this case, how about the following modification?

Modified script:

function removeEmptyRows() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("PB");
  var lastRow = sheet.getRange("A13:H"   sheet.getLastRow()).getDisplayValues().findIndex(r => !r.join(""))   13;
  var maxRows = sheet.getMaxRows();
  sheet.deleteRows(lastRow, maxRows - lastRow   1);
}

When this script is run, the empty row is searched from sheet.getRange("A13:H" sheet.getLastRow()).getDisplayValues() and the last row of the range "A13:H" is retrieved. And, using the retrieved last row, all rows after the last row are deleted.

Reference:

findIndex()