Hi All- I’m trying to make an applescript to use with excel 2008 to find all the columns with no data (blank) in an spreadsheet and delete them (shift left).
I was trying this:
delete (special cells of range (“A1:FF1”) type cell type blanks)
It didn’t work. Any suggestions?
Thanks!
AppleScript: 2.3
Browser: Safari 533.18.5
Operating System: Mac OS X (10.6)
Hi
your script works for me
tell application "Microsoft Excel"
activate
delete (special cells of range ("A1:FF1") type cell type blanks)
end tell
my system is Mac OS X (10.5.8)
you might have to go to the new c+ scripting
bills
Browser: Safari 533.16
Operating System: Mac OS X (10.5)
Thanks so much for the quick response and trying to help.
It’s so strange. When I run it all it does is simply delete cells G:FF shifting row 2 up.
It looks as though it was caused by the special formatting of the excel doc I downloaded because I reran the script on a newly created doc and it worked fine. I tried to manually go to blanks in excel on the doc and it didn’t work. Luckily I figured a workaround. Thanks again.
hi
Been playing with this script, If there is data in the empty column in the row below it will shift up,
ie: f1 empty, f2 data
If you change your range to “a1:ff4” and have no data in the blank columns of row1 then the whole range will shift left
a a a a a a
b b b
c c
d
b,c,d are all under an a(doesn’t come out that way when I summit)
becomes
a a a a a a
b b b
c c
d
hope this helps some
bills
Browser: Safari 533.16
Operating System: Mac OS X (10.5)
Bills,
Thanks for looking into that. Good catch on row shift. The spreadsheet is formatted in such a way that it doesn’t recognize the blank spaces when using the goto special function so I rigged it with the applescript below. For some reason it still doesn’t shift all of the cells unless I use multiple delete range commands. I’m certain there’s a cleaner, smarter, way to do this but for a first attempt at applescripting it works. 
tell application "Microsoft Excel"
activate
try
replace (range "A1:FD1" of worksheet "Results") what "" replacement "DELETEME" --¬search order by columns with match case
replace (range "A1:FD1" of worksheet "Results") what "DELETEME" replacement ""
delete range (special cells of row 1 type cell type blanks) shift shift to left
delete range (special cells of row 1 type cell type blanks) shift shift to left
delete range (special cells of row 1 type cell type blanks) shift shift to left
end try
end tell
Here is another one, someone else posted. It worked for me. It is longer however.
(*
Used Range is an unreliable measure for the end of data. For example if an entire column is formatted, then that entire column will be part of the Used Range.
The method FinalRowOfColumn below is one way to get the last data cell in a column.
(Note that the columnIndex argument is numeric and that a cell containing a formula that returns "" will be in the range.)
*)
global cnt_rows
tell application "Microsoft Excel"
set cnt_rows to count rows -- need all the rows, not just used, or it deletes most columns
tell used range of active sheet to set cnt_cols to count columns
set counter to 0
repeat cnt_cols times
set counter to (counter + 1)
set last_row_num to my FinalRowOfColumn(counter)
if last_row_num = 1 then
delete column counter
set counter to counter - 1 -- columns moved left, next is now # of deleted one
end if
end repeat
end tell
on FinalRowOfColumn(columnIndex)
tell application "Microsoft Excel"
try
return first row index of (get end of (cell (cnt_rows) of column columnIndex) direction toward the top)
on error
return 0
end try
end tell
end FinalRowOfColumn
What do you mean by “blank cells”?
Excel considers a cell that holds the formula =“” to be not blank.
tell application "Microsoft Excel"
set myRange to range "A1:FF1"
set value of myRange to get value of myRange
delete range (special cells of myRange type cell type blanks) shift shift to left
end tell