I am using “current region” to return all data surrounding a cell instead of “used range”. I then want to use the last cell address. I can get the address but cannot then use it to select a series of sub sets of that range. The message I get is in the heading,
tell application "Microsoft Excel"
set LastCell to last cell of (current region of cell "A1")
log LastCell
select range ("B4:" & LastCell)
end tell
The Excel AppleScript Reference tells us this:
“The current region is a range bounded by any combination of blank rows and blank columns. Read-only.”
Looks like cell A1 cannot be part of a ‘current region’. It cannot have a blank column to its left, or a blank row above it.
You are trying to combine unrelated things: a string and a cell reference. Take a look at your ‘log’ result… that’s what you’re proposing to concatenate with the string “B4:”. You’ll need to coerce lastCell into a string (e.g. “G6”).
FWIW, I don’t think microsoft would make it so you couldn’t use the edge rows of a spreadsheet and expect a script to work. You can prove it easily enough by moving the current region away from the sheet’s edges. You’ll get the same error. FYI, current region is an excel concept that works perfectly well with edge cells. It would be bizarre if applescript had a different conception of it. I imagine that the dictionary writer simply valued brevity.
Thank you both, I think I was over complicating the issue I had tried to coerce the “LastCell” variable to an “alias” but clearly was doing that incorrectly.
All I needed do was "get address " of the last cell and then I could get any sub set of the contiguous data. The 3rd line in the following code is irrelevant but added for completeness. The working range. in my example spanned B10:M17
Cell “A1” can certainly be part of a current region, my understanding is any current region will include from the starting cell all columns and all rows that have a value in the respective column and row.
tell application "Microsoft Excel"
set LastCell to last cell of (current region of cell "B10")
set LastCell to get address of last cell of (current region of cell "B10")
set FirstCell to get address of first cell of (current region of cell "B10")
select range ("E15" & ":" & LastCell) of active sheet
select range (FirstCell & ":" & "L16") of active sheet
end tell
Looks good.
FYI, by default, current region is a function of the active cell. For example, if you click on cell F12 in your example and then Edit > Go To… > Special… > Current region, the range B10:M17 will be selected. Of course, in a script you can specify the cell.
One other thing about get address… You can set its options to return an absolute or relative address, e.g. $B$10, $B10, B$10 or B10.
with column absolute without row absolute
without row absolute and column absolute
Unfortunately, when you get cell it always returns an absolute address — this has long annoyed me. However, you can double up on get address and return a relative address.
tell application "Microsoft Excel"
tell sheet 1 of workbook 1
set firstCell to get address of (first cell of range (get address of current region of cell "C12")) without column absolute and row absolute
--> "B10"
end tell
end tell
All in all, pretty neat. As per an MS’ example, you could even pair get address with the find command and have it look for some specific value when you don’t know the cell address in advance.
One last note on the current region. If you used cell F9 in your example — and row 9 is devoid of any value or formula — and get the current region, it will be range B9:M17.