How do you find the last used row in column A of Google Sheets

Question

How can I modify the below to give me the last row of column A. At the moment it calculates the last row of all the columns.

function Test() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var lr = ss.getLastRow();
  Browser.msgBox(lastRow);
}

I saw the below in another post and tried replacing mySheet with ss but that didn't give me the correct output.

var lastCell = mySheet.getRange(mySheet.getLastRow(),1).getNextDataCell(
  SpreadsheetApp.Direction.UP);

Does this answer your question? [Get the last non-empty cell in a column in Google Sheets](https://stackoverflow.com/questions/8116043/get-the-last-non-empty-cell-in-a-column-in-google-sheets) — user1790813, Apr 30 '20 at 19:35

score 2 · Accepted Answer · answered Apr 30 '20 at 17:41

2

Try this -

function Test() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();

  var colAValues = ss.getRange("A:A").getValues().filter(function(el){
    return el != "";
  });

  var lr = colAValues.length;
  Browser.msgBox(lr); // replaced "lastRow" with "lr"
}

Hope this helps but do please let know in case it doesn't :)

answered Apr 30 '20 at 17:41

Sourabh Choraria

2,255
25
64

1

Awesome, appreciate the help. – Ross Apr 30 '20 at 17:48

How do you find the last used row in column A of Google Sheets

1 Answers1