4

I'd like to invert my selection with AppleScript. This is analogous to Adobe Photoshop's (and other Adobe products) functionality "Invert selection (Ctrl/Cmd + I to deselect what was previously selected and select what was not selected)"

  1. I can select a group of cells with AppleScript.
  2. I can get selected range with AppleScript
  3. I can loop selected cells
  4. I'd like to select all cells that are not currently selected

Code

tell application "Microsoft Excel"
    repeat with item_cell from 1 to count large of selection -- all cells in selection
        --do stuff with selected cells
    end repeat
    
    -- index of selected cells technique, not sure whether helpful
    set sel_range to selection
    tell selection to set {rowIndex, columnIndex, rowCount, columnCount} to {get first row index of sel_range, get first column index of sel_range, get count rows of sel_range, get count columns of sel_range}
end tell

Originally Selected Cells

enter image description here

Desired Selection

(All cells in the sheet are selected that were not originally selected) enter image description here

  • When we talk about setting a fill color, “inverting a selection” could mean two different things. Is your goal to swap the text color with the background color in each selected cell? Or are you trying to select a set of cells that is not currently selected (and vice versa)? Where do you want this to end? (In your example screenshot, would you want your inverted selection to go past H18, or past H20? How about A200? ZZ999? XFD1048576?) – Synoli Feb 14 '17 at 19:02
  • 1
    @Synoli "set a set lf cells that is not currently selected" (and vice versa) The selection can be any cells in any particular order, hence the uneven selection in my example. If it were nice and neat, I could easily program it. – Jonathan Komar Feb 14 '17 at 20:51
  • So in your example, would H20 need to be selected, or would it not? – Synoli Feb 14 '17 at 21:42
  • 1
    @Synoli Yes, because it lies within the realm of the unselected. – Jonathan Komar Feb 14 '17 at 21:48
  • Maybe I'm missing something, but by invert my selection do you want Excel to swap the text colour with the background colour (as Synoli asked about) or do you just want the cell shaded a particular colour? – Monomeeth Feb 15 '17 at 0:56
1

Finally, I have a solution. Huge thanks to Monomeeth for the VBA version that set me on the right path. (And making it possible to do this under Windows too)

Works similarly to Monomeeth's version. It has a range considered for performance reasons. Set it by changing this line accordingly:

set range_considered to range "A1:Z100" of active sheet

Here it is:

-- use "intersect" to test whether the considered area overlaps with the selected area
-- use union to append non-selected cells to a new range

tell application "Microsoft Excel"
    set screen updating to false -- optimize performance
    --tell active sheet of active workbook
    set range_considered to range "A1:Z100" of active sheet
    set range_selected to selection
    set range_new_selection to "Nothing"

    -- setup ref vars for selection
    tell selection to set {range_selected_row_index, range_selected_column_index, range_selected_row_count, range_selected_column_count} to {get first row index of selection, get first column index of selection, get count rows of selection, get count columns of selection}
    -- setup ref vars for considered
    tell selection to set {range_considered_row_index, range_considered_column_index, range_considered_row_count, range_considered_column_count} to {get first row index of range_considered, get first column index of range_considered, get count rows of range_considered, get count columns of range_considered}

    -- go column by column and iterate each row in each column
    repeat with considered_col_step from range_considered_column_index to range_considered_column_count
        repeat with considered_row_step from range_considered_row_index to range_considered_row_count
            set range_this_cell to range (get address row considered_row_step of column considered_col_step of active sheet) of active sheet
            log range_selected
            try
                set range_test to intersect range1 range_this_cell range2 range_selected -- test for intersection with selected
                (*use try to detect error. Will fail if cell is within selection*)
                log "TRUE Intersected"
            on error
                (*Create or append to range_new_selection*)
                log "FALSE intersected"
                if range_new_selection is "Nothing" then
                    set range_new_selection to range_this_cell
                else
                    set range_new_selection to union range1 range_new_selection range2 range_this_cell
                end if
            end try
        end repeat
    end repeat
    try
        select range_new_selection
    on error
        log "Could not select new range."
    end try
    set screen updating to true -- optimize performance
end tell
| improve this answer | |
1
+50

I've been working on this question for a while and, hitting a brick wall, decided to try and get it to work using Visual Basic instead. My hope is that doing this will make it easier to get the code right within AppleScript, so I'm sharing the Visual Basic code in case it helps someone else to get an AppleScript version working - not that I've given up on this myself yet! :)

Sub InvertSheet()

Set s1 = Selection
Set s2 = Range("A1:Z100")
Set sinv = Nothing

For Each s In s2
If Intersect(s, s1) Is Nothing Then
If sinv Is Nothing Then
Set sinv = s
Else
Set sinv = Union(sinv, s)
End If
End If
Next

If sinv Is Nothing Then
Else
sinv.Select
End If
End Sub

NOTES:

  • I did come across a problem - namely that I couldn't find a way to get this to apply to the entire sheet without it causing Excel to hang for extremely long periods of time (probably because it has to check 1,048,576 rows and 16,384 columns) to do so.
  • To get around this problem I used Set s2 = Range("A1:Z100") to limit the area of the sheet that the code applies to to the range of A1:Z100. However, this can be changed to suit a larger area if needed.
  • I imagine trying to cover the whole sheet will be a problem within AppleSCript as well.
| improve this answer | |
  • This answer does not address the question. – Jonathan Komar Feb 16 '17 at 5:32
  • @macmadness86 I think it's not just me who doesn't understand what on earth you're asking. Mind clarifying a bit and then leaving a comment here so I can check it out more? – owlswipe Feb 16 '17 at 5:35
  • 1
    @macmadness86 Perhaps I should have been a little clearer, but what I really meant by the sentence If I've totally misunderstood your question, please let me know was for you to actually clarify the question, rather than just telling me my answer doesn't address it. If you don't mind, perhaps you could edit your question to include two screenshots (one before, one after) so we know what you want the code to do. Not trying to be difficult, just trying to clarify this so someone can help you. :) – Monomeeth Feb 16 '17 at 5:43
  • I thought synoli clarified the question pretty well in the comments. And I did adjust my question to make it clearer shortly after synoli's comments. I can add an screenshot showing the desired result, sure. – Jonathan Komar Feb 16 '17 at 5:52

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .