In VBA, the two argument form of Range returns the smallest continous range that contatins both elements
Range(Range"D4:K5", Range(“C12”)) = Range(“C4:K12”)
I can’t find the equiviant in Excel’s scripting library or the AppleScriptReference from the MicroSoft website.
I had to come up with the method RangeBoundedBy (error handling TBD). I hope that there’s a better way.
Thanks in advance.
tell application "Microsoft Excel"
set firstRange to range "D4:K5"
set secondRange to range "C12"
end tell
RangeBoundedBy(firstRange, secondRange)
-- range "[Workbook1]Sheet1!$C$4:$K$12" of application "Microsoft Excel"
on RangeBoundedBy(aRange, bRange)
tell application "Microsoft Excel"
set aFirstRow to first row index of aRange
set bfirstrow to first row index of bRange
set aFirstCol to first column index of aRange
set bFirstCol to first column index of bRange
end tell
set firstRow to Min(aFirstRow, bfirstrow)
set firstCol to Min(aFirstCol, bFirstCol)
set lastRow to Max(bottomRow(aRange), bottomRow(bRange))
set lastCol to Max(rightCol(aRange), rightCol(bRange))
tell application "Microsoft Excel"
return get resize (row firstRow of column firstCol) row size (lastRow - firstRow + 1) column size (lastCol - firstCol + 1)
end tell
end RangeBoundedBy
on rightCol(aRange)
tell application "Microsoft Excel"
return (first column index of aRange) + (count of columns in aRange) - 1
end tell
end rightCol
on bottomRow(aRange)
tell application "Microsoft Excel"
return (first row index of aRange) + (count of rows in aRange) - 1
end tell
end bottomRow
on Max(a, b)
if a < b then
return b
else
return a
end if
end Max
on Min(a, b)
if a > b then
return b
else
return a
end if
end Min