Ask a new question

Excel script for automation to the last row

I'm trying to autofill Column F (with formula in F2). The number of autofill entry depends on Column E, which is always dynamic. When I record my automation, the script gave me a static number (F500 in this case). What do I need to do to have Column F autofill to the last row in Column E. I tried scouring the internet, but none of the suggestions worked in the Excel Code Editor.

// Set range F2 on selectedSheet

  selectedSheet.getRange("F2").setFormulaLocal("=TEXT(E2,\"mm/dd/yyyy\")");

 // Auto fill range

  selectedSheet.getRange("F2").autoFill("F2:F500", ExcelScript.AutoFillType.fillDefault);

Any suggestion would be appreciated. I have zero coding knowledge.

|
Answer
Answer

function main(workbook: ExcelScript.Workbook) {


    let selectedSheet = workbook.getActiveWorksheet();


    // Set formula in F2


    selectedSheet.getRange("h2").setFormulaLocal("=TEXT(G2, \"mm/dd/yyyy\")");


    // Get the used range in Column E


    const eColumn = selectedSheet.getRange("G:G");


    const eUsedRange = eColumn.getUsedRange();


    // Determine the last row (1-based) in Column E


    let lastRow = 2; // Default to row 2 if no data found


    if (eUsedRange) {


        lastRow = eUsedRange.getRowIndex() + eUsedRange.getRowCount();


    }


    // Autofill the formula down to the last row


    const fillRange = `H2:H${lastRow}`;


    selectedSheet.getRange("H2").autoFill(fillRange, ExcelScript.AutoFillType.fillDefault);


}

=================================================================

function main(workbook: ExcelScript.Workbook) {


    let selectedSheet = workbook.getActiveWorksheet();


    // Set formula in F2


    selectedSheet.getRange("I2").setFormulaLocal("=F2=H2");


    // Get the used range in Column E


    const eColumn = selectedSheet.getRange("F:F");


    const eUsedRange = eColumn.getUsedRange();


    // Determine the last row (1-based) in Column E


    let lastRow = 2; // Default to row 2 if no data found


    if (eUsedRange) {


        lastRow = eUsedRange.getRowIndex() + eUsedRange.getRowCount();


    }


    // Autofill the formula down to the last row


    const fillRange = `I2:I${lastRow}`;


    selectedSheet.getRange("I2").autoFill(fillRange, ExcelScript.AutoFillType.fillDefault);


}

•Beware of Scammers posting fake Support Numbers here.

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

Answer
Answer

Try this one.

function main(workbook: ExcelScript.Workbook) { 

  let selectedSheet = workbook.getActiveWorksheet(); 

  // Set formula in F2 

  selectedSheet.getRange("F2").setFormulaLocal("=TEXT(E2, \"mm/dd/yyyy\")"); 

  // Get the used range in Column E 

  const eColumn = selectedSheet.getRange("E:E"); 

  const eUsedRange = eColumn.getUsedRange(); 

  // Determine the last row (1-based) in Column E 

  let lastRow = 2; // Default to row 2 if no data found 

  if (eUsedRange) { 

    lastRow = eUsedRange.getRowIndex() + eUsedRange.getRowCount(); 

  } 

  // Autofill the formula down to the last row 

  const fillRange = `F2:F${lastRow}`; 

  selectedSheet.getRange("F2").autoFill(fillRange, ExcelScript.AutoFillType.fillDefault); 

} 

•Beware of Scammers posting fake Support Numbers here.

1 person found this reply helpful

·

Was this reply helpful?

Sorry this didn't help.

Great! Thanks for your feedback.

How satisfied are you with this reply?

Thanks for your feedback, it helps us improve the site.

How satisfied are you with this reply?

Thanks for your feedback.

 
 

Question Info

Last updated April 13, 2025 Views 194 Applies to: