Excel – Two Types Of Transpose

In Excel – Sorted With AppleScript I mentioned I might have a go at transposing rows and columns in an Excel spreadsheet with AppleScript.

I said there were two things I could try – each with a different use case:

• Transposing Rows And Columns In A Spreadsheet
• Transposing Rows And Columns In A Graph

I – after quite a bit of effort – achieved both. Again, in the spirit of saving others time, I’m posting them here.

Transposing Rows And Columns In A Spreadsheet

The following code transposes the selected cells into cell A1 of a new work sheet. I’m using a new worksheet as it saves worrying about whether the transposed range will overwrite cells you don’t want it to.¹ You can always cut and paste from the new worksheet to where you actually want it to go.²

tell application "Microsoft Excel"
    activate
    set r to selection
    copy range r
    tell active workbook
        set tempSheet to (make new worksheet at end)
    end tell
    paste special range "A1" with transpose
end tell

Having ensured Excel is activated the code:

1. Creates a range r from the selected cells.
2. Copies the cells to the clipboard.
3. Creates a new worksheet at the end of the active workbook.
4. Pastes the copied cells into cell A1 of this new workbook.

One thing I found didn’t work was attempting to paste special with transpose into the original selection; It simply didn’t transpose cells and columns.

Transposing Rows And Columns In A Graph

About half the time Excel decides that each row in the data is a series when I want each column to be a series (and vice versa).

There is a dialog ( “Select Data…”) you can pop up – but it requires taking your hands off the keyboard and clicking a button “Switch Row/Column”. This takes time and gets old really fast when you’re working with graphs all day long.

So I discovered a faster way – which could be automated with a hot key (perhaps with Keyboard Maestro or BetterTouchTool. Here’s the code:

tell application "Microsoft Excel"
    activate
    -- Get the last chart created
    set s to active sheet
    set chartCount to count of charts of chart objects of s
    set c to chart of (last chart object of s)

    -- Get the "plot by" direction for that chart and
    set p to (plot by of c)

    -- Set "plot by" to the opposite orientation
    if p is by columns then
        set plot by of c to by rows
    else
        set plot by of c to by columns
    end if

end tell

Here’s how it works.

1. Under the Get the last chart created comment are three lines of code which yield the last chart created – in variable c.
2. Then variable p is set to the value of the plot by attribute of the chart.
3. Based on whether its value is “by columns” or “by rows” the same attribute is set to the converse value.

The detection of the last chart created is generally what I want: My use case is to create a chart, decide I don’t like it, and then immediately transpose it.³

Conclusion

Once again problems can usefully solved – but with much experimentation. And through extensive use of the excellent Script Debugger. In particular, if you assign an object to a variable you can see its properties.

I hope this has proven useful for somebody; It certainly has for me.

And, having mentioned BetterTouchTool and Keyboard Maestro above, I’ve automated both of these using a Stream Deck. In detail I use a keystroke action that kicks off automation.