I am trying to filter and delete data while avoiding time consming loops. The more I can use excel’s normal functions the better. I am using the autofilter command which works almost instantly. Then I need to delete based on the results. In excel I can select visible cells, so there must be a way to script that action, but i can’t find anything. Has anyone been able to do this?
here is my script so far:
tell application "Microsoft Excel"
tell active sheet
autofilter range (range ("A1")) field "3" criteria1 ("*#*")
select special range (visible)
end tell
end tell
I also tried “goto” instead of select since navigating to “select special” through the edit menu goes through “find” then “go to”
The autofilter scripting works great, filtering for all records in column C that contain “#,” although preference would be to get rid of the entirety of any row that contains any text in column C without using loops.
Any ideas?
I appreciate any help I can get.
P.S. I can bring up the dialogue box using:
show (get dialog dialog select special)
but I need for this to be automated so others with less computer literacy can use the script I am writing.
tell application "Microsoft Excel"
activate
tell active sheet
show all data
delay 2 -- optional, to improve visibility while testing
with timeout of 8 seconds
autofilter range range "A:D" field 3 criteria1 "*#*"
set xy to special cells range "A2:D4" type cell type visible
delete range xy shift shift up
end timeout
end tell
end tell
Thank you. This looks brilliant, but I am getting a parameter error with this line highlighted:
special cellsrange “A2:D4” type cell type visible
" error “Microsoft Excel got an error: Parameter error.” number -50"
The other problem I am having when I did this using the dialogue box to test it is saving the header row. I have tried hiding the header row befor deleting. I have also tried copying it to paste later. It still gets deleted even if hidden for some reason. When I do a copy and paste function without the extra programming, copying and pasting does fine. when I separate copy and paste by a few otehr line commands there is nothing to paste.
That error is an excel error, not an applescript one.
I’d try making a new script with just the ‘special cells’ line, setting the spreadsheet up manually so that the # cells are hidden and then run the script to see what it returns.
As to the other problem, I’m not I sure I understand it clearly. But you can (as in my example) only select the range from row 2 onward when deleting. Then you don’t need to worry about deleting row 1 as well.
This is so wonderful! I really appreciate the help.
Now I know what to look for in an error between applescript and Excel! It is like spending 5 hours on a calculus problem only to find out that I was making a calculator error!
I’m so glad you mentioned using timeouts as well. I was timing out on “autofit column” until it occurred to me to add “of used range.”
The various elements of the script are now working great and are super fast even with a few thousand records.
I found out that using “**” with nothing in the middle works with anything that contains a letter or punctuation, but I have a handfull of records left that are just numbers in column c.
I can then filter out the numbers using the criteria1 as “>1”. Is there a way to connect the two ilters then select the range and delete just once or do I need to set xy, delete, then select xy1 and delete again?
autofilter range range "A:D" field 3 criteria1 ">1"
autofilter range range "A:D" field 3 criteria1 "**"
One filter sets up to get rid of text items, the other sets up the number items. After getting rid of both of them I will have exactly what I need to work with.
( My copy/paste question was based on a work-around I was doing with dialogue boxes and “audience participation” where I could not designate the starting row, only to delete visible rows. Maybe something with the dialogue boxes was eating the copied text, but I don’t have to worry about that now that you helped me get this working the way I originally intended)
You can use up to two criteria. Place an operator between them. You can look up the operator syntax in the dictionary but in this case it would be ‘autofilter or’… so it will filter any rows that match either of the criteria.
autofilter range range "A:D" field 3 criteria1 "*#*" operator autofilter or criteria2 "*fun*"
Alternatively, as you suggest, you could have processed each criteria separately.
Regarding the timeout… if you look at the results of some commands (not necessarily in your script) you will notice that when you use an entire column, applescript may generate a list that includes every row — all 1048576 of them. And of course, if you have multiple columns involved, then you end up with even more massive lists. Note that while excel will handle this effortlessly, applescript generally does not.
To avoid this, I often employ used range or some arbitrary limit on rows (e.g. A1:G200). Despite this, some actions seem to enter an endless loop. I forget the command now that convinced me to change my ways but now I pretty much always use timing out whenever the spreadsheet has a vague range. I also often use intersect to set boundaries on a range to be processed, e.g. intersect range1 range "A:G" range2 used range.
It is almost ready for prime time! Thank you so much for your hlep, with scripting as well as with process.
To prevent error messages, I have placed information into the column so there will always be something to select for the xy variable.
set value of cell ("C" & (lastrow + 1)) to "42"
Then when I filter for “>1” and set both the filter range and the special cells range to include the extra row, someone running the program twice on the same worksheet won’t generate an error.
I learned Basic as a kid on an Atari 800, and I have not done programming since. I am very familiar with how Excel works, and this is pretty much using a script instead of a keyboard/mouse to operate Excel. Still, it is amazing how learning one language makes it easier to learn another. The concepts are all the same, the syntax and terminology are different.
I now have a program that even the volunteer with almost no computer experience can run, answer a couple of menu choices for how to organize the data and what data to include, and end up with the core of useful information on an otherwise unwieldy spreadsheet – all organized in a matter of seconds. That means more time actually using the data for important work. I wish there were an easy way to share this with volunteers in other areas who do the same work, but code signing makes that difficult.
Again, thanks for the help. I am glad that I found this resource.