This script will find the last used row.

 

When I use the script ?

I use this script in different ways, sometime to paste new cells at the last row, sometime to know the last row ID for other actions for instance.

 

How to create the script ?

Read How to create, edit and select an Office Script in an excel report

 

How to create the button to associate it with the script ?

Read How to create a button and associated it to an Office Script in an excel report

 

How is/are the script(s) ?

Copy the code below and paste it into your script. You will see my comments in green if exist so follow the help to adapt to your need.

function main(workbook: ExcelScript.Workbook) {
  // change sheet1 by yours
  let sheet = workbook.getWorksheet("Sheet1");
  let lastRow = sheet.getUsedRange().getRowCount();
  // change column A by yours
  // to select the empty cell after the used cell, change lastRow by (lastRow+1)
  sheet.getRange("A" + lastRow).select();
}              
              

If in the sheet, there are “false” empty cells, this script will include them. To exclude them, use this one below:

function main(workbook: ExcelScript.Workbook) {
  // change sheet1 by yours
  let mainsheet = workbook.getWorksheet("Sheet1");
  // change column A by yours
  let lastRow = getLastRow(mainsheet,mainsheet.getRange("A:A"));
  // to select the empty cell after the used cell, change lastRow by (lastRow+1)
  mainsheet.getRange("A" + lastRow).select();
}
// update mainsheet by yours
function getLastRow(mainsheet: ExcelScript.Worksheet, rng: ExcelScript.Range) {
  let lastCell = mainsheet.getCell(rng.getEntireColumn().getCellCount() - 1, rng.getColumnIndex());
  if (lastCell.getValue().toString().length <= 0) {
    return lastCell.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;
  }
}