I’m trying to clear thousands of rows from a large excel doc, based on the presence of a string of characters within a certain column. The script runs without error, but no rows are deleted. An help would be appreciated.
tell application “Microsoft Excel”
tell worksheet “Sheet1” of active workbook
set badValue to “target” as string
set columnCount to (count of columns of used range of active sheet)
repeat with i from 1 to columnCount
set cellValue to content of cell (“B” & i) as string
if cellValue contains badValue then
delete row i
end if
end repeat
end tell
end tell
content of cell "B1"
-- should be
value of cell "B1"
Some observations:
script is deleting rows, but you’re giving it a column count
as string is not needed, those variables are text already.
A warning: when deleting rows (columns) starting from the top (left) cannot work. The count changes as rows/columns are deleted, and the script will skip rows: when row 99 is deleted row 100 will now become row 99, and it will never be tested. Script will also fail when it tries to get rows/columns that are no longer there. Here’s how to work from the bottom/right:
repeat with counter from theCount to 1 by -1 -- stepping backwards
-- interesting stuff happens here
end repeat
Getting rows of used range may give you a ridiculously large number. Get the actual last used row like so:
tell application "Microsoft Excel"
tell refTargetSheet to set indexLastUsedRow to (first row index of (get end cell 101 of column 1 direction toward the top))
end tell
Change the number 101 to some reasonable value, or put rows of used range in there.