Select Excel spreadsheet cells in an autofiltered range

Question

I'm attempting to use AppleScript to loop through cells in a manually auto-filtered range in an Excel spreadsheet. The manual selection includes auto filtered cells in a single column. My code correctly displays the contents of the first cell in the selected range. However, it displays the value of every cell between the first auto filtered cell and the last one, the values of which should be excluded (i.e., hidden on the spreadsheet) by the filter. I want to restrict the script output to only those displayed cells that meet the filter criteria.

tell application "Microsoft Excel"
    set myRange to selection --> selected cells of 1 col of autofiltered range
    set rowCount to (count every row of myRange)
    repeat with j from 1 to rowCount
        set cellVal to value of cell j of myRange
        display dialog "Current cellVal: " & cellVal
    end repeat
end tell

I don‘t know AppleScript, but if it were an Excel function, you would use SUBTOTAL instead of COUNT. — Daniel Rust

Pat_Morita Pat_Morita · Accepted Answer · 2018-03-21T23:22:24

That doesnt work out of the box as filtered cells are still visible to AS. They are just hidden visually in Excel. You need to copy the visible cells to a New Spreadsheet. Before you copy them you first need to choose a special option "Select visible cells only". Unfortunately, Microsoft changes the position of that option from version to version. Google for it and your version. Then copy the cells over to a new document. Execute your script on that document.

Select Excel spreadsheet cells in an autofiltered range

1 Answers