Office Script for auto fill to last row of adjacent column

boxboxbox

New Member
Joined
Oct 3, 2024
Messages
4
Office Version
  1. 365
Platform
  1. Windows
I'm trying to make an automate script for my worksheet and having problems with the autofill function. I have 2 columns of data A and B...i have a formula (=TEXTJOIN(", ",,A2:B2) at the top of column C (cell C2) that i would like to have fill down to the last row of data from column B. The number of rows changes each time i need to run the script, so i can't set a specific range.


Essentially I am looking for the script that is equivalent to double clicking the bottom right corner of a cell to make the data auto fill down.

I didn't even know scripts existed until 2 days ago so i am very new to this
 

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
How about:

JavaScript:
function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    selectedSheet.getRange("C2").setFormulaLocal("=TEXTJOIN(\", \",,A2:B2)");
    let lastRow = selectedSheet.getUsedRange().getLastRow().getRowIndex() + 1;
    let rng = "C2:C" + lastRow;
    selectedSheet.getRange("C2").autoFill(rng, ExcelScript.AutoFillType.fillDefault);
}
 
Upvote 0
Solution
Deleted as read it as vba code rather than Office Script (should have read the title properly)
 
Last edited:
Upvote 0
thank you, that worked! But what if I wanted to auto fill another column beside it? or 2? or 5? it worked with the first (column C) when i change the cell references, to use the next few columns i get an error
 

Attachments

  • Screenshot 2024-10-03 152727.jpg
    Screenshot 2024-10-03 152727.jpg
    20.8 KB · Views: 4
Upvote 0
How about:

JavaScript:
function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    selectedSheet.getRange("C2").setFormulaLocal("=TEXTJOIN(\", \",,A2:B2)");
    let lastRow = selectedSheet.getUsedRange().getLastRow().getRowIndex() + 1;
    let rng = "C2:C" + lastRow;
    selectedSheet.getRange("C2").autoFill(rng, ExcelScript.AutoFillType.fillDefault);
}
thank you, that worked! But what if I wanted to auto fill another column beside it? or 2? or 5? it worked with the first (column C) when i change the cell references, to use the next few columns i get an error

i've fixed the redeclare error by changing the 'last row' to 'bottomrow' and similar for the others and that went away. but now i'm getting a new error...
 

Attachments

  • Screenshot 2024-10-03 153703.jpg
    Screenshot 2024-10-03 153703.jpg
    39 KB · Views: 11
Upvote 0
You are filling out Cell D1 and Filling it down from D2. Isn't D1 supposed to be D2, or is that the column header? Also at the bottom of your code you are still referencing rng when it should be rang like @MARK858 said as well as the bottomRow instead of LastRow
 
Upvote 0

Forum statistics

Threads
1,224,271
Messages
6,177,619
Members
452,786
Latest member
k3calloway

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top