Excel - remove one range from another??

Scripting Forums AppleScript | Mac OS X
#1

I am trying to create a script to remove one range from another. In other words, if I have a range of cells named “Tablehawk” which is “a2:f1000”, and I want to sort every row except for “a2:f2”, how do I remove the row in order to do the sort???

There is a union of ranges, but I cannot seem to find any reference to the opposite of a union? I am sure the answer is obvious, but at the moment, the Applescript that I am using is doing what I want, but is throwing up this error:

error “Can’t make «class X117» "TableHawk" of «class XwSH» "Lessons" of «class 1172» of application "Microsoft Excel" into type number.” number -1700 from «class X117» “TableHawk” of «class XwSH» “Lessons” of «class 1172» to number

Here is my Applescript for reference:

tell application “Microsoft Excel”
open workbook workbook file name “Filename.xlsx”
tell worksheet “Lessons” of active workbook
select range “TableHawk”
insert into range range “a3:f3” shift shift down
set value of cell “a3” to “AA”
set value of cell “b3” to “21/03/10”
set value of cell “c3” to “23:30”
set value of cell “d3” to “21/03/10”
set value of cell “e3” to “23:45”
set value of cell “f3” to “Chris”
sort (b - (range “a2:f2”)[/b]) key1 (range “B1”) order1 sort ascending key2 (range “C1”) order2 sort ascending
end tell
end tell

Any advice would be gratefully received. Thank you…

#2

Hi,

the numeric operator minus (-) considers only numeric (and date) values.
I recommend to specify the sort range separately, which is actually the whole TableHawk range except the first row


tell application "Microsoft Excel"
	set sortRange to range "B2:F1000"
end tell
#3

Thank you for your reply, Stefan:

The reason that I was trying to do it with the name of the table, “TableHawk”, is because I will not know the full range of the table when I am running the script. For example, it could be “a3:f1543”. I need to have a script that will operate the sort without the first row of titles - no matter how big the table range will be…

#4

You can make Excel sort a range and ignore the first row by using the header argument.

tell application "Microsoft Excel"
	sort range "A18:B21" key1 range "A8" order1 sort ascending header header yes
end tell
#5

Thank you, mikerickson,

That works perfectly!!! Progress!!!