Question:

How to get the last cell with data for a given column in TypeScript?

Problem

In Excel Office Scripts, I need to get a range starting from cell B2 all the way down to the last cell in column 2 that contains data, contiguously.

That is the equivalent, of selecting B2 and clicking Ctrl + Down.

What kind of method does that?

This is my code, and I want to replace .getRange("B2:B4") by something automatic.

function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

// Set font bold to true for range B2:B4 on selectedSheet

selectedSheet.getRange("B2:B4").getFormat().getFont().setBold(true);

}

Solution

Try

function main(workbook: ExcelScript.Workbook) {

let selectedSheet = workbook.getActiveWorksheet();

// Get continuous data range

let dataRange = selectedSheet.getRange("B2").getExtendedRange(ExcelScript.KeyboardDirection.down);

console.log(dataRange.getAddress());

dataRange.getFormat().getFont().setBold(true);

// Get non-continuous data range

let lastCell = selectedSheet.getRange("B:B").getLastCell().getRangeEdge(ExcelScript.KeyboardDirection.up).getRowIndex();

let dataRange2 = selectedSheet.getRangeByIndexes(1,1,lastCell,1)

console.log(dataRange2.getAddress());

dataRange2.getFormat().getFont().setBold(true);

}

Microsoft documentation:

Nisha Patel

Your Answer

Submit

Related Questions