Forum Discussion

johnellis1971200's avatar
johnellis1971200
Copper Contributor
Jan 08, 2024
Solved

Modify Excel Macro Script to Accommodate a Dynamic Range of Data2 -- The Sequel

Below is the script that Microsoft Excel created automatically for me, when I used the "Record Actions" button of the "Automate" menu selection. This works fine, at the moment.  But, now, I'm trying...
excel
  • JKPieterse's avatar
    JKPieterse
    Jan 09, 2024

    johnellis1971200 Here is a sample script to which I have added a function that returns the last used row of any range:

    function main(workbook: ExcelScript.Workbook) {
	const sht = workbook.getWorksheet("Sheet2");
	const lastRow = getLastRow(sht, sht.getRange("H:H"));
	console.log(lastRow);
	sht.getRange("H2:H" + lastRow).select();
}

function getLastRow(sht: ExcelScript.Worksheet, rng: ExcelScript.Range) {
	let lastCell = sht.getCell(rng.getEntireColumn().getCellCount() - 1, rng.getColumnIndex());
	return lastCell.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;
}
JKPieterse's avatar
JKPieterse
Silver Contributor
Jan 09, 2024

johnellis1971200 Here is a sample script to which I have added a function that returns the last used row of any range:

function main(workbook: ExcelScript.Workbook) {
	const sht = workbook.getWorksheet("Sheet2");
	const lastRow = getLastRow(sht, sht.getRange("H:H"));
	console.log(lastRow);
	sht.getRange("H2:H" + lastRow).select();
}

function getLastRow(sht: ExcelScript.Worksheet, rng: ExcelScript.Range) {
	let lastCell = sht.getCell(rng.getEntireColumn().getCellCount() - 1, rng.getColumnIndex());
	return lastCell.getExtendedRange(ExcelScript.KeyboardDirection.up).getRowIndex() + 1;
}

Resources