Moving from Microsoft Office VBA to AppleScript:
MacTech's Guide to Making the Transition

Page Prev and Page Next buttons at bottom of the page.

April, 2007
Page 62

Here's the AppleScript version:

tell application "Microsoft Excel"

     tell active sheet

          set lastRowNum to first row index of ¬

              (get end (cell 1 of row (count rows)) direction toward the top)

          set rDelete to missing value

          repeat with i from 1 to (count cells of range ("A2:A" & lastRowNum))

              --that's column A from 2nd cell to bottom used cell                                   

              set rCell to (cell i of range ("A2:A" & lastRowNum))

              if value of rCell = value of (get offset rCell row offset -1) then

                   if rDelete = missing value then

                        set rDelete to rCell

                   else

                        set rDelete to union range1 rDelete range2 rCell

                   end if

              end if

          end repeat

          if rDelete ≠ missing value then delete entire row of rDelete

     end tell

end tell

Once again we set a variable to the last row number to make things more manageable in the repeat statement. In AppleScript, a variable that is not defined is not equal to null (our rarely used version of Nothing), it instead causes an error if called. Normally we would just trap that error with a try/on error block, but in this case it would also put the union statement in the same try block and so perhaps catch a different error (leading to a never-ending loop that keeps setting rDelete to the first cell), or would require some very complex error management, plus would invert the order of things to come, making comparisons more difficult.

So it makes good sense to initialize rDelete to a value it couldn't possibly have otherwise. We could set it to anything we like that's not an Excel range, and common initializations would be to "" or 0. But we might as well use AppleScript's handy expression for something undefined but which does not error – missing value. (You'll be seeing that a lot if either you or Excel makes a mistake in scripting.) It's not exactly null, but it's just what we want here.

Checking back if necessary to Working with Columns and Rows, and bearing in mind that in just the same way you cannot get (every cell of someRange) nor set a variable to it, but you can count cells of someRange (!), we have to construct the repeat loop to avoid the repeat with rCell in (cells of someRange) syntax, and equally to avoid setting rCell to item i of (cells of someRange). [someRange in this case being range ("A2:A" & lastRowNum) throughout.]

Instead we must set it up so we refer to