Forum Discussion
Solved
Office script version of VBA for column autofit?
hi everyone,
Do you know if there's a way to mimic the behaviour of the VBA command cells.entirecolumn.autofit using Office Script?
I've managed to get this behaviour on a particular table (and on the entire worksheet), but only when you trigger it manually.
I'm trying to get excel to autofit the columns as you type data in (as per the VBA behaviour), rather than be triggered.
The reason I need Office Scripts is I need this to work in Excel online.
thanks ☺️
- You can use something like:
selectedSheet.getRange("D8:J8").getFormat().autofitColumns();
See:
https://learn.microsoft.com/en-gb/office/dev/scripts/tutorials/excel-read-tutorial
- You can use something like:
selectedSheet.getRange("D8:J8").getFormat().autofitColumns();
See:
https://learn.microsoft.com/en-gb/office/dev/scripts/tutorials/excel-read-tutorial- Nice. That works as part of:
function main(workbook: ExcelScript.Workbook) {
//define what the selectedSheet means
let selectedSheet = workbook.getActiveWorksheet();
//apply column width auto-format to the selectedSheet object
selectedSheet.getRange("A1:BZ1000").getFormat().autofitColumns();
}
Is there any way to get this to run after each and every data input to the sheet? Or can these scripts only be run via manual trigger?
- Is the VBA code triggered by an event? e.g. worksheet_change? If so that won't work in Office Scripts as Office Scripts doesn't currently support events.
- hi there,
No, with the VBA code it just applies to the worksheet under any circumstances - you type anything into the sheet and it will flex the column widths accordingly, live as you type.
I don't know VBA - is this sort of real-time behaviour governed by an 'event'?- Could you share the VBA code? If so I would be able to confirm
