AppleScript Excel – How to Invert Selection

applescriptms officescript

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

Best Answer

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
Related Question