Re: How can I delete every empty row of an Excel document?
Re: How can I delete every empty row of an Excel document?
- Subject: Re: How can I delete every empty row of an Excel document?
- From: Paul Berkowitz <email@hidden>
- Date: Mon, 24 Feb 2014 07:59:52 -0800
- Thread-topic: How can I delete every empty row of an Excel document?
Title: Re: How can I delete every empty row of an Excel document?
You can go backwards, as you recommend, through each row of 'used range' , using
delete range row x shift shift up
if you find row x to be empty. (You also may have to ensure that you are deleting row x of the worksheet, not row x of the used range - I don't know. Perhaps not.)
You should also, after experimenting and finding if the script works,
set screen updating to false
before you start, and then et it to true at the end of the script. You then won't see the script removing each row one at a time, only all at once, but it should be much faster.
I haven't tested this method, but it's worth trying. I looked to see if there might be some "magic command" in Excel that deletes all unused rows in a range and avoids a repeat loop, but I couldn't find one.
--
Paul Berkowitz
From: "koenig.yvan" <email@hidden>
Date: Mon, 24 Feb 2014 14:18:26 +0100
To: AppleScript-Users <email@hidden>
Subject: Re: How can I delete every empty row of an Excel document?
I don't own Excel but I'm puzzled;
My understanding is that your code replace formulas by their result.
(1) Am'I right ?
(2) If I am right, is it an acceptable behavior ?
I was said that Excel dictionary is huge.
So I'm wondering if there is a way to scan the used range starting from the bottom and delete empty rows ?
If there is not, maybe you may use an empty column and
fill its cells with the row num if the row is not empty
leave the cells empty when the row is empty.
After that, sort the table according to this column to push the empty rows to the bottom
At last, clear the cells of the column.
Yvan KOENIG (VALLAURIS, France) lundi 24 février 2014 14:17:26
Le 24/02/2014 à 12:13, Thomas Fischer <email@hidden> a écrit :
Hi Bert,
it took a me while to get back to your question.
I still have problems to get at the coordinates of Excel ranges inside of AppleScript, but the script below should get rid of empty lines within the used range of an Excel sheet.
As in your example it works on the current active sheet open in Excel.
tell application "Microsoft Excel"
set sheetData to value of used range of active sheet -- grab all data from Excel
set newData to {}
set emptyData to {}
repeat with theRow in sheetData
if theRow as string ≠ "" then
copy theRow to the end of newData
else
copy theRow to the end of emptyData
end if
end repeat
set the value of used range of active sheet to newData & emptyData
end tell
By the way, the 192 lines come from the used range:
get areas of used range of active sheet
--> {range "'[Folder_08 copy.xls]Blatt1'!$A$1:$V$192"}
I didn't figure out in which sense the last empty lines are "used".
Best
Thomas
Am 21.02.2014 um 13:39 schrieb Bert Groeneveld:
Hi Thomas, thank you for your reaction,
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.
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden
_______________________________________________
Do not post admin requests to the list. They will be ignored.
AppleScript-Users mailing list (email@hidden)
Help/Unsubscribe/Update your Subscription:
Archives: http://lists.apple.com/archives/applescript-users
This email sent to email@hidden