Modify Excel Macro Script to Accommodate a Dynamic Range of Data

[johnellis1971200]

Hi:

Below is the VBA 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 B2:B6.

I want to change B6 to represent the last row in column B containing data.

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

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

Thanks!

John

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  let _2024Forecast = workbook.getWorksheet("2024Forecast");
  // Paste to range B2:B6 on selectedSheet from range A4:A8 on _2024Forecast
  selectedSheet.getRange("B2:B6").copyFrom(_2024Forecast.getRange("A4:A8"), ExcelScript.RangeCopyType.values, false, false);
  // Paste to range F2:F6 on selectedSheet from range B4:B8 on _2024Forecast
  selectedSheet.getRange("F2:F6").copyFrom(_2024Forecast.getRange("B4:B8"), ExcelScript.RangeCopyType.values, false, false);
  // Paste to range H2:H6 on selectedSheet from range F2:F6 on selectedSheet
  selectedSheet.getRange("H2:H6").copyFrom(selectedSheet.getRange("F2:F6"), ExcelScript.RangeCopyType.all, false, false);
}

[#2 · Accepted solution]

Hello @johnellis1971200,

To modify the range to extend to the last row of data in column B, you can use the getUsedRange method. This method returns the used range, which is the smallest range that encompasses any cells that have a value or formatting. Here’s how you can modify your script:

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  let _2024Forecast = workbook.getWorksheet("2024Forecast");
  
  // Get the last row in column B containing data
  let lastRow = selectedSheet.getUsedRange().getRowCount();

  // Paste to range B2:B(lastRow) on selectedSheet from range A4:A(lastRow+2) on _2024Forecast
  selectedSheet.getRange(`B2:B${lastRow}`).copyFrom(_2024Forecast.getRange(`A4:A${lastRow+2}`), ExcelScript.RangeCopyType.values, false, false);
  
  // Paste to range F2:F(lastRow) on selectedSheet from range B4:B(lastRow+2) on _2024Forecast
  selectedSheet.getRange(`F2:F${lastRow}`).copyFrom(_2024Forecast.getRange(`B4:B${lastRow+2}`), ExcelScript.RangeCopyType.values, false, false);
  
  // Paste to range H2:H(lastRow) on selectedSheet from range F2:F(lastRow) on selectedSheet
  selectedSheet.getRange(`H2:H${lastRow}`).copyFrom(selectedSheet.getRange(`F2:F${lastRow}`), ExcelScript.RangeCopyType.all, false, false);
}

This script will copy the data from the “2024Forecast” sheet to the active sheet, extending to the last row of data in column B of the active sheet. Note that the getUsedRange method considers any cell with data or formatting, so if there are any formatted cells beyond your data in column B, the lastRow might be larger than expected. If this is the case, you might need to consider a different approach to find the last row with data in column B.