0

i'm converting some VBA Macros to Office Script and I've just found this trick:

lastRow_in_t = Worksheets("in_t").Range("A1048576").End(xlUp).Row

What could be a good start to do this line of code in Typescript/Office Script

I have no clue where to start, if someone could give me a hint, i'd be glad

braX
  • 11,506
  • 5
  • 20
  • 33
Teco
  • 5
  • 3

2 Answers2

2

you need to use getUsedRange() with true in the parameter to accept values only, and then use getLastCell()

function main(workbook: ExcelScript.Workbook){
  let wrkSh = workbook.getWorksheet("in_t")
  let lastRow = wrkSh.getRange("A:A").getUsedRange(true).getLastCell();
  console.log(lastRow.getValue())
}

if you check in the console, you should have the same number as VBA if you were to debug.print it.

Ethan
  • 808
  • 3
  • 21
0

If you can turn your range into a table, you can use this code to get the dimensions of the range (row count and column count).

let tableValuesArray = await Excel.run(async (context) => {
    let columnRange = context.workbook.tables.getItem("MyTable").getRange();
    columnRange.load("values");
    await context.sync();
    return columnRange.values;
});

let rowsCount = tableValuesArray.length - 1; //minus the header
let columnCount = tableValuesArray[0].length;
Antoni Pietrzak
  • 1
  • Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Aug 01 '23 at 00:16