Getting Last Row Of Column - Excel Javascript-CodePudding

I have the following code, which I expected would return 5 for rowCount for range A1:A5 but it returns the UsedRange lastrow even when I have "filtered" to a subrange.

I tried using ws.getColumn(0), but this threw an error.

How I can get the last row of column A without syncing the entire used range?

var ws = context.workbook.worksheets.getActiveWorksheet();
var rng = ws.getRange("B1:B10")
rng.values = "B"
var rng = ws.getRange("A1:A5")
rng.values = "A"
await context.sync()
var col_one_rng = ws.getUsedRange(true).getColumn(0)
col_one_rng.load(['rowCount'])
col_one_rng.select() //Selects "A1:A10"
await context.sync();
var rowCount = col_one_rng.rowCount
console.log('rowCount:'   rowCount) //10

CodePudding user response：

Try this instead... get the range for column A and then get the usedRange for that range:

var ws = context.workbook.worksheets.getActiveWorksheet();
ws.getRange("B1:B10").values = "B";
ws.getRange("A1:A5").values = "A";

var col_one_rng = ws
  .getRange("A:A")
  .getUsedRange(true)
  .load(['rowCount']);

col_one_rng.select(); // Selects "A1:A5"

await context.sync();

console.log('rowCount:'   col_one_rng.rowCount) // 5