Applescript & Excel for Mac

Scripting Forums AppleScript | Mac OS X
#1

Hello everyone!

This is my first post in the community!

I am using applescript to post process a bunch of data in Excel and i am facing a problem regarding autofill or fill down commands.
Lets say that i have a Column A filled with data and a column B where i put a formula to the cell B1. How can i autofill the rest of the column B and stop to the number of rows of adjacent column A?
I have tried to use:
fill down range “B:B” but it goes to down infinity and fills blank cells with zeros.

What i need is a sort of fill down range “B1:Bx” where x is the number of rows of column A. The length of column A varies from file to file. How do i do this?

Thanks for any help

#2

I think this should work

tell application "Microsoft Excel"
	set lastRowOfA to first row index of (get end (row -1 of column 1) direction toward the top)
	
	fill down (get resize range ("B1") row size lastRowOfA)
	
end tell

However, this might be a touch faster

tell application "Microsoft Excel"
	set lastRowOfA to first row index of (get end (row -1 of column 1) direction toward the top)
	
	set myFormula to formula r1c1 of range ("B1")
	set formula r1c1 of (get resize range ("B1") row size lastRowOfA) to myFormula
end tell