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

Question

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 to be proactive.

You'll see that one example of a range used in this script is L2:L10.

I want to change L10 to represent the last row in column L containing data.

For instance, if the last row containing data is 120, I want this range to be L2:L120.

What syntax, then, do I use in this script to have the range L2:L("whatever the last row containing data is")?

The same question applies in the last row of code where column C is mentioned.

Thanks!

function main(workbook: ExcelScript.Workbook) {

let table_ExternalData_1 = workbook.getTable("Table_ExternalData_1");

// Sort on table: table_ExternalData_1 column index: '1'

table_ExternalData_1.getSort().apply([{key: 1, ascending: true}]);

// Sort on table: table_ExternalData_1 column index: '0'

table_ExternalData_1.getSort().apply([{key: 0, ascending: false}]);

// Sort on table: table_ExternalData_1 column index: '1'

table_ExternalData_1.getSort().apply([{key: 1, ascending: true}]);

let selectedSheet = workbook.getActiveWorksheet();

let _2024Q2BudgetForecastServi = workbook.getWorksheet("2024Q2BudgetForecastServi");

// Paste to range L2:L10 on selectedSheet from range C4:C12 on _2024Q2BudgetForecastServi

selectedSheet.getRange("L2:L10").copyFrom(_2024Q2BudgetForecastServi.getRange("C4:C12"), ExcelScript.RangeCopyType.all, false, false);

jkpieterse · Accepted Answer

johnellis1971200&nbsp;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;
}

Forum Discussion

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

Share

Resources