Why does my script only delete some of the matching rows?

You are almost certainly looping top-down. deleteRow() shifts all subsequent row indices up by one, so consecutive matches never both get deleted. Flip the loop to start at getLastRow() and decrement to 1.

How do I match values in a specific column instead of searching the whole row?

Set searchColumn to the 1-based column number you want (1 for A, 2 for B, etc.) and call sheet.getRange(i, searchColumn).getValue(). The snippet above already does this.

Can I delete rows where any column in the row contains the value?

Yes. Replace the single getValue() call with sheet.getRange(i, 1, 1, sheet.getLastColumn()).getValues()[0], then use Array.prototype.indexOf or a for-loop over that array to check every cell in the row before calling deleteRow(i).

The script runs but nothing is deleted. What should I check?

First confirm the value matches exactly: trailing spaces, capitalization, and numeric-vs-string type all break equality. Wrap both sides in String().trim() to rule out whitespace. Also check that searchColumn points at the right column. Add Logger.log(cell) inside the loop and check View > Logs to see what the script is actually reading.

// Sheets · Apps Script

Delete rows containing a specific value in Google Sheets.

A step-by-step Apps Script guide to deleting every row that contains a specific value in Google Sheets, with a bottom-up loop that avoids the silent index-shift bug that skips half your matches.

“I need to delete every row in my sheet that contains a specific value, without manually hunting them down one by one.”

The script

copy · paste · trigger

deleteMatchingRows.gs

Apps Script

// Delete every row where column B contains the target value.
// Run from Tools > Script editor, then Macros > deleteMatchingRows.
function deleteMatchingRows() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var targetValue = 'CANCEL';
  var searchColumn = 2; // column B
  var lastRow = sheet.getLastRow();

  for (var i = lastRow; i >= 1; i--) {
    var cell = sheet.getRange(i, searchColumn).getValue();
    if (String(cell).trim() === targetValue) {
      sheet.deleteRow(i);
    }
  }
}

Generate yours

Need a variant? Gnaw writes a custom version from one sentence — fields, triggers, edge cases handled.

Walkthrough

Why top-down deletion breaks silently

When you call sheet.deleteRow(i) on row 5, every row below it shifts up by one. Row 6 becomes row 5, row 7 becomes row 6, and so on. Your loop variable has already moved past the new row 5, so it never gets checked. If two matching rows sit back-to-back, you delete the first and skip the second every time. The bug is reproducible but easy to miss because the sheet looks partially cleaned.

The fix is to iterate from the last row upward, toward row 1. Deleting row 10 does not affect rows 1 through 9, so the loop index always points at valid data.

Adapting the target value and column

Change targetValue to whatever string you are matching: a status code like 'REFUNDED', a department name, a flag column value. The search is case-sensitive by default; if your data is mixed-case, swap the comparison to String(cell).trim().toLowerCase() === targetValue.toLowerCase().

searchColumn uses a 1-based column number (1 = A, 2 = B, 3 = C). If you would rather reference by letter, sheet.getRange('B' + i).getValue() works identically. I keep the numeric form in a utils file because it is easier to compute programmatically when the column position might change.

The loop starts at sheet.getLastRow() rather than a hard-coded number, so it handles sheets of any length without adjustment.

Handling partial matches and large sheets

The snippet uses strict equality, which means 'CANCEL NOW' would not match 'CANCEL'. If you want substring matching, replace the equality check with String(cell).indexOf(targetValue) !== -1.

For sheets with thousands of rows, calling getValue() inside the loop issues one API call per cell, which hits Apps Script's 6-minute execution limit around 3,000-4,000 rows. The faster pattern is to read all values into an array first with sheet.getDataRange().getValues(), collect the row indices that match, then delete them in reverse order. That trades hundreds of Spreadsheet service calls for one read and N deletes, which is meaningfully faster past a few hundred rows.

Want a custom version?

Describe your sheet and the rule you want. Gnaw writes the Apps Script — fields, triggers, edge cases — in one shot.

Generate yours Browse BullPack

FAQ

4 questions

Why does my script only delete some of the matching rows?: You are almost certainly looping top-down. deleteRow() shifts all subsequent row indices up by one, so consecutive matches never both get deleted. Flip the loop to start at getLastRow() and decrement to 1.
How do I match values in a specific column instead of searching the whole row?: Set searchColumn to the 1-based column number you want (1 for A, 2 for B, etc.) and call sheet.getRange(i, searchColumn).getValue(). The snippet above already does this.
Can I delete rows where any column in the row contains the value?: Yes. Replace the single getValue() call with sheet.getRange(i, 1, 1, sheet.getLastColumn()).getValues()[0], then use Array.prototype.indexOf or a for-loop over that array to check every cell in the row before calling deleteRow(i).
The script runs but nothing is deleted. What should I check?: First confirm the value matches exactly: trailing spaces, capitalization, and numeric-vs-string type all break equality. Wrap both sides in String().trim() to rule out whitespace. Also check that searchColumn points at the right column. Add Logger.log(cell) inside the loop and check View > Logs to see what the script is actually reading.

// one good script a week