title: Google Sheets - How can I find the first row number containing specific text, and then clear/delete all content to end?
body: <p>I'm currently importing a bunch of data and then splitting it into multiple columns and then attempting to clear or delete any erroneous rows (it's a raw data import that contains a bunch of scrap rows).</p>
  
  <p>So far I've got the data imported, split, &amp; sorted.  Now I'm trying to find the row number, based on a value in column A, and then select all rows to the end of the sheet to either delete or clear the content.</p>
  
  <p>I'm to the point where all of my data has been split into the columns I need (A:J) and sorted so that all relevant data is at the top (it's a variable data set) so now I'm just trying to find the first row that contains "----------------------" as this will be my first 'garbage' row.</p>
  
  <pre><code>outputrange.setValues(output);
  pull1.deleteRows(1, 40);
  pull1.getRange(2, 1, pull1.getLastRow()-1, 
  pull1.getLastColumn()).activate().sort({column:2, ascending: true});
  
      var removalValues = range.getValues()
      for (var j=0; j&lt;removalValues.length; j++) {
        var rowArray = removalValues[j];
        for (var k=0; k&lt;rowArray.length; k++) {
          var columnValue = rowArray[k];
          if (rowArray[0] === "----------------------") {
  
            var rowNumber = i;
            pull1.getRange(rowNumber, 1, 1, pull1.getLastColumn()).activate()
          }
        }
      }
  </code></pre>
  
  <p>I've attempted the code above to loop through and find the correct cell reference, and just temporarily highlight the row so I make sure it's functioning correctly.  Currently this part of my code processes but otherwise doesn't do anything.  Really I just need something that will look through my data in column A and find the matching data, then return the row number for me so that I can apply it to other formulas.</p>
  
  <p>Edit: I updated my code using some additional resources and came up with the following.  It <em>seems</em> to work correctly but I'm not sure if it's the most efficient solution:</p>
  
  <pre><code>        var outputrange = pull1.getRange(startRow, 1, LR-startRow+1, 10)
          outputrange.setValues(output);
          pull1.deleteRows(1, 40);
          pull1.getRange(2, 1, pull1.getLastRow()-1, 
          pull1.getLastColumn()).activate().sort({column:2, ascending: true});
  
          var rangeData = pull1.getDataRange();
          var lastColumn = rangeData.getLastColumn();
          var lastRow = rangeData.getLastRow();
          var searchRange = pull1.getRange(1,1,lastRow-1,lastColumn-1);
          var removalValues = searchRange.getValues();
  
          for (j=0; j &lt; lastColumn-1; j++) {
            for (k=0; k &lt; lastRow-1; k++) {
              if(removalValues[k][j] === "----------------------") {
                pull1.getRange(k+1, 1, pull1.getLastRow(), 10).deleteCells(SpreadsheetApp.Dimension.ROWS);
  
              }
            }
          }
  </code></pre>