Forum Discussion

James_Furmage's avatar
James_Furmage
Copper Contributor
Sep 11, 2022

Excel online script for adding multiple new rows

Hello everyone, I'm wondering if there is a way to create a Script that will add one or more rows at the users desired location? I know this is a very simple thing to do in excel anyway but this might help some of my colleagues who struggle with excel. I managed to do this with a VBA which worked great but that doesn't seem to work on shared documents in the online version of Excel. Would a script be able to do this? Thanks

Office Scripts
  • MichelleRanMSFT's avatar
    MichelleRanMSFT
    Icon for Microsoft rankMicrosoft
    Oct 05, 2022

    Hi James_Furmage,

     

    Thanks for reaching out and apologies for the delayed response! You could create an Office Script with the following code and attach it to a button so that your colleagues can easily use it:

     

    function main(workbook: ExcelScript.Workbook) {
	let selectedCell = workbook.getActiveCell();
	selectedCell.getEntireRow().insert(ExcelScript.InsertShiftDirection.down);
}

     

     This script will insert a row above the selected cell. Hopefully it helps - let me know if you have any questions!

     

    Best,

    Michelle

    • EdwardJ-RITS's avatar
      EdwardJ-RITS
      Copper Contributor
      Jun 18, 2024

      MichelleRanMSFT 

      Thanks for this mate, this is what I was after looked high and low looking all over the internet.

      Just wondering if there is a way that from what you have said you can also able to copy the first column in that row to the row that you are freshly inserting?

      Cheers

      • MichelleRanMSFT's avatar
        MichelleRanMSFT
        Icon for Microsoft rankMicrosoft
        Jun 21, 2024

        EdwardJ-RITS this script should insert a new row under the selected cell and copy the value from the first column:

        function main(workbook: ExcelScript.Workbook) {
    let selectedRow = workbook.getActiveCell().getEntireRow();
    let newRow = selectedRow.getEntireRow().insert(ExcelScript.InsertShiftDirection.down);
    newRow.getCell(0, 0).setValue(selectedRow.getCell(0, 0).getValue());
}

        Let me know if that helps, or if you have any questions! 

Resources