It is probably very simple but I need to delete 2 columns the Column letters of which I do not know at the start. I can delete them one at a time using column numbers but would prefer to select both columns and then delete them.
this selects a single column
[AppleScript]
tell application “Microsoft Excel”
delete (column 13)
end tell
You can use the union command to create a range object from several columns and then delete it all at once.
use AppleScript version "2.4" -- Yosemite (10.10) or later
use scripting additions
tell application "Microsoft Excel"
tell active workbook
tell active sheet
set cols to union range1 column 13 range2 column 14
delete cols
end tell
end tell
end tell
The union command can take up to 30 range parameters and they don’t have to be contiguous.
Whoa! I did no know if this. Thank you!
Ugh. No longer works. The below now only deletes column 2.
use AppleScript version “2.4” – Yosemite (10.10) or later
use scripting additions
tell application “Microsoft Excel”
tell active workbook
tell active sheet
set cols to union range1 column 2 range2 column 50
delete cols
end tell
end tell
end tell
Works perfectly for me.
Two things to test,
Comment out (or remove) the delete cols line so you have just the set cols… line and run the script. Do you get a result like this:
range "[Workbook1]Sheet1!$B:$B,$AX:$AX" of application "Microsoft Excel"
If you do, then replace delete cols with select cols. Does it select the two columns?
Hmm. Here’s what I get if I comment out the delete line. Looks good.
range “‘[test delete sheet .xlsx]Sheet1’!$B:$B,$AX:$AX” of application “Microsoft Excel”
However, when I add back in the delete line, only column 2 is deleted. See the before and after screen shots attached.
I can’t really see the results for AX column but it seems fine. Can you test it with columns 2 and 6 or something like that?
Yep, works for me (using a more reasonable second column index that we can actually see on screen):
tell application "Microsoft Excel"
tell active workbook
tell active sheet
set cols to union range1 column 2 range2 column 10
delete cols
end tell
end tell
end tell
That’s just plain weird. I lowered the second term in the range first to 12 and then to 6. Running either of those deletes only two columns. If I change the first term in the range to 1 it still removes just two columns. Maybe it’s the version of Excel I’m using: Microsoft Exel for Mac Version 16.81. Or, does union only join two columns? The AS dictionary entry is confusing.
Maybe I’m going about this wrong. I have a very big spreadsheet. I am trying to remove a bunch of columns. They are not all contiguous so I have one method for a bunch of random columns:
set deleteList to {38, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 4, 3, 2}
repeat with x in deleteList -- cycle through list of columns to delete
delete column x
end repeat
And I was trying the "union"method to remove two big contiguous blocks of columns. One is really big, from like 50 to 2000. I can remove the columns in these blocks by iterating and deleting a column at a time but that takes forever. I started with the below for deleting blocks of columns but it caused Excel to crash.
tell application "Microsoft Excel"
tell active workbook
tell active sheet
set seleteRange to "B:F"
delete range seleteRange
end tell
end tell
end tell
I’m running out of ideas. I know I have done this in the past. I will dig around and see if I can find an old script. Possibly something changed in Excel or Apple Events since I have last done this.
Ugh. This makes my head hurt. I just tried the below and it works.
tell application "Microsoft Excel"
activate
tell active sheet
set deletRange to range ("A:BDH")
delete deletRange
end tell
end tell
The only difference I had one too many range references in the script from the last post.
It joins whatever ranges you give it. So if you only give it 2 columns, it will only join those 2 columns. If you want all the columns between 2 columns, you will need to give the union command the appropriate range.
So to join four ranges, you do:
set cols to union range1 column 40 range2 column 41 range 3 column 42 range 4 column 45 …
That doesn’t seem like much of a labor saver over just specifying the range with the “X:Z” syntax. Maybe union is useful for other things.
Closing this out with a complete and working script. The script allows some flexibility in deleting columns in Excel.
You can delete a batch of contiguous ranges using the colon-based range definition (either A1:B2 or just the column or row name if you are deleting entire columns or rows).
If you have a bunch of random columns, you can create a list and iterate through that.
NOTES:
• This can be adapted to delete rows instead of columns.
• Always delete from right to left and/or from bottom to top in Excel. Because Excel renames cells/rows/columns after they are deleted, the indexing of anything above or to the right of the deleted cells gets renamed.
tell application "Microsoft Excel"
tell active workbook
tell active sheet
set deleteRange to range ("BJ:BXX") --contiguous columns
delete deleteRange
set deleteList to {38, 18, 17, 16, 15, 14, 13, 12, 11, 10, 9, 8, 7, 6, 4, 3, 2} --individual non-contiguous columns
repeat with x in deleteList
delete column x
end repeat
end tell
end tell
end tell
Per the OP, they don’t want (or aren’t able) to use “X:Z” format, but need to refer to columns by their number not their letter.
If you have the letters, though, then yeah this would be simpler for joining discontiguous columns:
set rng to union range1 range "A:C" range2 range "G:G" range3 column 12
delete rng
or even this, taking advantage of how Excel lets you combine range addresses:
set rng to union range1 range "A:C,G:G" range2 column 12
delete rngMakes sense. The letter naming convention gets very annoying after “AA.”
Instead of letters for column names (like “A:C,G:G”), one can also use an alternative notation (like “R1:R3,R7:R7”).
This uses “R1C1” (Row 1 Column 1) notation. So “A1” becomes “R1C1” and “C5” becomes “R5C3”. Joel Spolsky (who worked on the Excel team once) names the “A1” style baby-style. Not sure about that, but it makes kind of sense…
If you want, you can also let Excel show column numbers instead of names: Preferences > Calculation, and check the “Use reference style R1C1” checkbox. (not sure about the names, since I’ve translated my Dutch localized version).
I see one problem (just found out by creating this reply): the identifiers are localized. “Column” in Dutch is “Kolom”, so in English you see “R1C1” but I see “R1K1”.