I have a workflow in which an applescript processes Excel documents. Sometimes the Excel document contains empty rows. I want to get rid of all empty rows. I guess there must be some "simple" command in the Excel Applescript dictionary to remove empty lines. I can't figure it out so I wrote following workaround script:
tell application "Microsoft Excel"
set sheetData to value of used range of active sheet -- grab all data from Excel
-- repeat loop to delete all empty rows:
set temp_sheetdata to {}
repeat with r from 1 to (length of sheetData)
set thisRow to item r of sheetData
if thisRow as string ≠ "" then
set the end of temp_sheetdata to thisRow
end if
end repeat
-- clear entire contents of Excel document:
set numberofrows to count rows of used range of active sheet
repeat numberofrows times
delete row 1 of active sheet
end repeat
-- set new range:
set used_range to "A1:V" & (length of temp_sheetdata) as string -- I know that column V is always the last column
set value of range used_range of active sheet to temp_sheetdata -- push all data back to Excel
end tell
I attached an example Excel file. Strange thing about this Excel file with 143 rows is that when I (manually) save it as a tab delimited text file I get 192 lines of text (instead of the expected 143). I can't find out where the additional 49 lines come from. Fortunately, my workaround script fixes this :-)
I hope to find out if there is a smarter way to delete empty rows from an Excel document. That's why I post it on the mailing list.
Bert.