AppleScript & Excel: Delete rows containing certain word

Question

Level 1

0 points

AppleScript & Excel: Delete rows containing certain word

I am having to use Applescript to delete rows in excel containing "Delete" without removing rows containing "Delete (old)". I have a script but it keeps deleting "Delete" and "Delete (old)". Which I can not have. And if we can find a way to make the looks faster that would be a great bonus because this is really slow when I am using it for 1000 rows.

This is what I have so far...

tell application "Microsoft Excel"

activate

selectrange "A1"

set FoundResult to false

try

set FoundCell to (find range "1:350" what "Delete")

set FoundResult to true

deleteentire row of FoundCell

end try

set i to 1

repeat while i ≤ 350

try

set FoundCell to (find range "1:350" what "Delete")

set FoundResult to true

deleteentire row of FoundCell

end try

set i to i + 1

end repeat

end tell

iMac, Mac OS X (10.6.7), with Cinema Display

Posted on Aug 22, 2013 11:40 AM

Reply

Answer 1

turingtest2

Level 10

278,263 points

Aug 22, 2013 1:19 PM in response to doowecec

I've yet to wrap my head around AppleScript fully (and I don't have Excel for Mac) but I suspect you'd need to ensure that find is matching the entire cell, if that is possible. Alternatively check the value/contents of FoundCell is "Delete" before deleting the entire row.

Offhand I can't see why you have two loops?

If the entry for "Delete" is in a particular column, couldn't you quickly sort the entire sheet on that column, select & delete the relevant rows, the revert to the initial sorting criteria?

tt2

Reply

Answer 2

doowecec Author

Level 1

0 points

Aug 22, 2013 2:58 PM in response to turingtest2

Hey Turingtest2,

Thanks for the insight, I truly appreciate any help or ideas I can get.

First: Technically find is matching the entire cell since the only thing in it is "Delete". And this could be in 2-100 rows so we wouldn't be able to manually change the content of the cell before running the script every time.

Second: I don't know why I had 2 loops either so I have removed one and it runs much faster. Thanks for bringing that to my attention. 🙂

Third: The only thing with sorting the column then selecting & deleting the rows means, the spreadsheet would have to have the exact amount of "Delete" rows everytime which will never happen, unfortunately. This is for a spreadsheet of orders that we be creating 7-8 times a day and each one will be completely different but the word will always be the same.

Bonus: I was thinking maybe there is a way to "skip", "pass", or "do not delete" the ones with "Delete (old)" . Some how have it check the contents of the column in the row to make sure it doesn't not have (old) prior to deleting. And just get the contents of FoundCell, check for OLD and react appropriately. But I have no idea of how to do that. :-/

tell application "Microsoft Excel"

activate

selectrange "A1"

set FoundResult to false

set i to 1

repeat while i ≤ 350

try

set FoundCell to (find range "1:350" what "SAP-CRM")

set FoundResult to true

deleteentire row of FoundCell

end try

set i to i + 1

end repeat

end tell

Reply

Answer 3

turingtest2

Level 10

278,263 points

Aug 22, 2013 4:23 PM in response to doowecec

Back on Windows the Excel find function can be set to be case sensitive or not, and match the entire cell conents or not. I don't know if you can define those options within AppleScript but your results so far would suggest that a partial match on cell contents counts as a hit.

A workaround would be to globally replace Delete (old) with some other text that has the same meaning, but doesn't contain the word Delete, or perform further tests on the value of FoundCell before deleting the row.

Again, I'm not familiar with the layout of your data or the precise behaviour of AppleScript but I would have thought something along these lines would be closer to the mark...

tell application "Microsoft Excel"

activate

set FoundResult to true

repeatwhileFoundResult

set FoundResult to false

try

set FoundCell to (find range "<Named Range>" what "<Unique Search Term>")

set FoundResult to true

deleteentire row of FoundCell

end try

endrepeat

end tell

This might also work...

tell application "Microsoft Excel"

activate

repeat

try

set FoundCell to (find range "<Named Range>" what "<Unique Search Term>")

deleteentire row of FoundCell

on error errStr number errorNumber

exit repeat

endtry

endrepeat

end tell

tt2

Reply

Answer 4

doowecec Author

Level 1

0 points

Aug 28, 2013 3:59 PM in response to doowecec

Hey guys!

Thanks a ton for your ideas and suggestions, they have definitely helped me with a couple other things I was having trouble with. Which is AWESOME!

I have been working with several groups reguarding this question and here is the end result that works, posting it so anyone can use this script. By adding look atwhole to the equation it ignored the rows with (OLD) and skipped to the next one.

Final working script:

tell application "Microsoft Excel"

activate

selectrange "A1"

set FoundResult to false

set i to 1

repeat 350 times

try

set FoundCell to (find range "1:350" what "Delete" look at whole)

if FoundCell is null then exit repeat

--or if that doesn't work--

--if FoundCell is "" then exit repeat

deleteentire row of FoundCell

on error

exit repeat

end try

end repeat

end tell

Thanks again!

Reply