Delete Rows in Excel with Applescript

Question

I am trying to delete specific rows in excel using applescript. I want to delete the rows based on a value in a given column. Within the given column, I have a list of values (ex. 3001, 3004, 5003, ect.) that I want to keep.

For instance, if any of my values (3001, 3004, 5003) are in column C I want to keep the row that contains that value. If the row doesn't contain one of my values in column C I want to delete that row. I found this applescript on here but all it does is delete everything but row 2 I can't get it to keep my range of values.

I substituted field 10 for field 3 since I was working with column C and I changed "(Not NY State)" to "(Not 3001)" but that didn't work. Also how do I need to list the values, do I list them as "(3001, 3004, 5003)" or "(3001; 3004; 5003) or what?

tell application "Microsoft Excel"

    set autofilter mode of active sheet to false
    set lastRow to first row index of last row of used range

    autofilter range row ("1:" & lastRow) field 10 criteria1 "(Not NY State)"
    select row ("2:" & lastRow)
    delete selection -- if you want to delete 
    #clear contents selection -- if you only wish to clear the data

end tell

Darrick Herwehe · Accepted Answer · 2013-05-15 15:23:23Z

1

I would just iterate through the rows looking at values:

set myValues to {3001, 3004, 5003}
tell application "Microsoft Excel"
    tell active workbook

        (* Code to count the amount of rows *)

        repeat with i from rowCount to 1 by -1
            set cellValue to value of cell ("C" & i)
            if cellValue is not in myValues then
                delete row i
            end if
        end repeat
    end tell
end tell

Note that it iterates backwards through the rows. When you delete a row, Excel will shift the rows up. Going backwards ensures that every row is processed.

EDIT BASED ON COMMENTS:

For better performance, try to get all values in the column and loop through those instead of getting the cell value from Excel during every iteration.

set columnValues to my quickExcelList("C1:C" & rowCount, return)
repeat with i from rowCount to 2 by -1
    if item i of columnValues is not in myValues then
        tell application "Microsoft Excel"
            delete row i
        end tell
    end if
end repeat

--Shane Stanley's routine to get tab/return delimited text from an Excel sheet
--------------------------------------------------------------------------------
on quickExcelList(theRange, theDelim)
    tell application "Microsoft Excel"
        tell active sheet
            copy range range theRange
            set copiedText to the clipboard
        end tell
    end tell

    set theList to strLib's explode(copiedText, theDelim)
    return theList
end quickExcelList

edited May 15, 2013 at 15:23

answered May 14, 2013 at 20:57

Darrick Herwehe

3,7631 gold badge25 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Chase Mitchell Over a year ago

Thanks that worked but it takes it awhile b/c of the loop. Is there a shorter way to do it? Also, how to exclude the first row with my headings? This is code I used. set myValues to {3102, 3104, 3105} tell application "Microsoft Excel" set rowCount to (((count of rows of used range of active sheet)) + 1) repeat with i from rowCount to 1 by -1 set cellValue to value of cell ("C" & i) if cellValue is not in myValues then delete row i end if end repeat end tell

Darrick Herwehe Over a year ago

To exclude the header, change your loop to "repeat with i from rowCount to -2 by -1." That will keep it from processing the first line. As far as performance, it might help to get all values up front, then loop in that. That would cut your calls to Excel in half. I've also noticed that if you hide Excel while a script is running, it runs 2-3x as fast.

Chase Mitchell Over a year ago

Thanks for all the help! It works great but it turned out to be "+2 by -1" on the rowcount instead of "-2 by -1". One more question, what do you mean by get all the values up front then loop it in?

Darrick Herwehe Over a year ago

My bad. You're right. I wasn't reading my own code close enough. I don't remember the exact code off the top of my head, but something like 'value of range "C2:C<rowCount>"' would give you all values in the column. Then, you could loop through those values instead of getting the cell value in each iteration.

Chase Mitchell Over a year ago

Thanks again for all the help! I tried to include variations of 'value of range' script before my 'repeat with i from rowcount to 2 by -1' line but I never could get it to work. I get this error message 'error "Microsoft Excel got an error: The object you are trying to access does not exist" number -1728 from value of range "C2:<rowcount>"'. I'm very new to applescript and appreciate all the basic help!

Collectives™ on Stack Overflow

Delete Rows in Excel with Applescript

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related