AppleScript that find last cell in Excel range

Questions & Suggestions
1

Attached AppleScript works fine if row number 46949 is the last row in an Excel range. But the last cell in range can vary. Is there a way for the script to find the last cell in a range. So 'A2:A46949' would become something like 'A2:ALastCell'?

test.kmmacros (1.9 KB)

Keyboard Maestro Export
Keyboard Maestro Export2052×982 143 KB

2

You generally create a range object then work with that -- the script you show returns a list containing the value of every cell in the range object "A2 through A46949". The last cell in the range is A46949.

Change the line to include "A2:A10" and the last cell in the range is A10.

So you determine what the last cell in a range is -- you just need to decide how you do that! Is the range the current selection? Does it end with the last cell in the column that has a value? Or that has a specific value? Randomly? Something else?

3

@layo , are you trying to find the last cell in column A?

tell application "Microsoft Excel"
	tell active sheet of active workbook -- or whatever sheet you want
		tell range "A:A" to set rng to cell (count of rows) -- (1)
		tell rng to set rng to get end direction toward the top -- (2)
	end tell
end tell

Line (1) grabs the very last cell that Excel allows in a column (usually row 1048576). Essentially the same as writing A1048576.

Line (2) moves that range up to the first non-blank cell it finds, leaving you with the last cell in the column. Final result: A46949 (or whatever the last cell actually happens to be in your particular spreadsheet).

1 Like