SOLVED

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

Copper Contributor

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);

1 Reply
best response confirmed by johnellis1971200 (Copper Contributor)
Solution

@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;
}
1 best response

Accepted Solutions
best response confirmed by johnellis1971200 (Copper Contributor)
Solution

@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;
}

View solution in original post