I’m new to applescript, is there any code to find and alert the user for rows, columns and even the content in the cell is hidden in the all sheets of excel file.
your help is much appreciated!
This is my code but I got result for active sheet only.
tell application "Microsoft Excel"
repeat with i from 1 to (count of worksheets of active workbook)
set theSheet to name of sheet i of active workbook
tell (get used range of sheet i of active workbook)
repeat with j from 1 to count rows
if hidden of row j of used range of sheet i then
display dialog (theSheet & " " & "row " & j & " is hidden")
end if
end repeat
repeat with k from 1 to count columns
if hidden of column k of used range of sheet i then
display dialog (theSheet & " " & "column " & k & " is hidden")
end if
end repeat
end tell
end repeat
end tell
I posted this elsewhere but I see this question here now as well, so….
The key is [format]activate object[/format].
use scripting additions
tell application "Microsoft Excel"
activate
set shAll to worksheets of active workbook
repeat with ss in shAll -- loop sheets
activate object ss
set nSht to name of ss
set cc to columns of used range of ss
repeat with c in cc -- loop columns
if hidden of c is true then
set fci to first column index of c
display dialog "Column " & (fci as text) & " of " & nSht with title "Hidden columns"
end if
end repeat -- of columns
set rr to rows of used range of ss
repeat with r in rr -- loop rows
if hidden of r is true then
set fri to first row index of r
display dialog "Row " & (fri as text) & " of " & nSht with title "Hidden rows"
end if
end repeat -- of rows
end repeat -- of sheets
end tell
Gonna need the technical term for ‘junk contents’.
That said, apparently there is a sneaky number format for ‘hiding’ the content of cells. Never noticed this before but it is in Excel’s stupid help — if you set the number format to Custom ‘;;;’ then the cell will appear blank. I say sneaky because it isn’t one of the listed options and there’s nothing to suggest that any number of semi-colons would have any meaning.
So, on the assumption that those constitute hidden cells, here is a script that goes through the used range of the active sheet and assembles a range containing its hidden cells. You could modify it —based on the above script— to cycle through worksheets if you like. It has no error handling so it will break if your worksheet is without hidden cells.
use scripting additions
tell application "Microsoft Excel"
set usr to used range of active sheet
-- display dialog (get address usr)
--> $A$1:$E$30
set y to count of cells in usr --> 150 cells
set hil to {} -- hidden item list
-- the agonizing part
repeat with x from 1 to y -- loop laboriously through used range checking for number format ;;;
if number format of cell x of usr is ";;;" then
set end of hil to cell x of usr
end if
end repeat
hil -- every cell with number format ;;;
--> {cell "$B$11" of used range of active sheet of application "Microsoft Excel", cell "$D$11" of used range of active sheet of application "Microsoft Excel", cell "$E$11" of used range of active sheet of application "Microsoft Excel", cell "$B$23" of used range of active sheet of application "Microsoft Excel", cell "$B$24" of used range of active sheet of application "Microsoft Excel"}
-- build range containing every hidden cell (ur)
set c1 to areas of item 1 of hil
set x2 to item 1 of c1
set lc to length of hil -- count of hidden cells
set ur to x2 -- set initial range1 of union
repeat with y from 2 to lc -- union of hil's cells
set c1 to areas of item y of hil
set x2 to item 1 of c1
set ur to union range1 (ur) range2 x2
end repeat
select ur
--> range "[worko1.xlsx]Shee2!$B$11,$D$11:$E$11,$B$23:$B$24" of application "Microsoft Excel"
set gaur to get address ur
display dialog gaur with title "Hidden cells of active sheet" giving up after 8
--> $B$11,$D$11:$E$11,$B$23:$B$24
set the clipboard to gaur
end tell
And of course, you could accept the answer on the other site.
I mean, it makes sense. Custom formats have four sections delineated by semicolons, with the last section being text format. And if all of those are blank then logically the cell won’t display anything. I just never thought to try this before. Could come in handy.
This should be a lot faster for finding hidden columns and rows. When I tested with a 300x300 Excel worksheet with randomly hidden columns/rows, it was around 1 second vs 15 for the code that loops through all columns and rows and checks their hidden property. On small datasets it probably wouldn’t matter, though.
use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use scripting additions
(*
Returns an expanded range of integers from a start and a length
Given: {start, length}
Returns: {start, ... start + (length-1)}
Example: {3,7} -> {3,4,5,6,7,8,9}
*)
on expandRange:range
set {rangeStart, rangeLength} to range
if class of rangeStart is not integer then error "rangeStart must be an integer"
if class of rangeLength is not integer then error "rangeLength must be an integer"
set expandedRange to {rangeStart}
repeat rangeLength - 1 times
set rangeStart to rangeStart + 1
set end of expandedRange to rangeStart
end repeat
return expandedRange
end expandRange:
(*
Removes items in list1 that are also present in list2
*)
on removeItemsInListInOrder:list2 fromList:list1
set set2 to current application's NSSet's setWithArray:list2
set set1 to current application's NSMutableOrderedSet's orderedSetWithArray:list1
set1's minusSet:set2
return (set1's array()) as list
end removeItemsInListInOrder:fromList:
on run
-- set things up
set colRanges to {}
set rowRanges to {}
tell application "Microsoft Excel"
activate
set allSheets to worksheets of active workbook
repeat with aSheet in allSheets
--activate object aSheet
tell worksheet (name of aSheet)
-- get a list of all column and row numbers on the sheet
set allColumns to (my expandRange:{1, count of columns of used range})
set allRows to (my expandRange:{1, count of rows of used range})
-- get the ranges of the visible columns by looking at row 1
set visibleCols to areas of (special cells (row 1 of used range of active sheet) type cell type visible)
repeat with colArea in visibleCols
set end of colRanges to {first column index of colArea, count of columns of colArea}
end repeat
-- get the ranges of the visible rows by looking at column 1
set visibleRows to areas of (special cells (column 1 of used range) type cell type visible)
repeat with rowArea in visibleRows
set end of rowRanges to {first row index of rowArea, count of rows of rowArea}
end repeat
end tell
end repeat
end tell
(*
At this point, we have:
- a list of all column numbers
- a list of all row numbers
- a list of the ranges of the visible columns
- a list of the ranges of the visible rows
Now we need to turn the visible ranges into lists of column/row numbers
*)
set visibleColumns to {}
repeat with colRng in colRanges
set visibleColumns to visibleColumns & (my expandRange:colRng)
end repeat
set visibleRows to {}
repeat with rowRng in rowRanges
set visibleRows to visibleRows & (my expandRange:rowRng)
end repeat
(*
Now that we have our four lists, we can just subtract the
visible columns/rows from all columns/rows to give us
the hidden columns/rows
*)
set hiddenColumns to my removeItemsInListInOrder:visibleColumns fromList:allColumns
set hiddenrows to my removeItemsInListInOrder:visibleRows fromList:allRows
(*
We end up with list of indices for the hidden columns/rows
Do whatever you like with them
*)
end run
Unfortunately, I haven’t figured out a better way to get individual hidden cells. The code Mockman posted above may be as good as it gets.
First, that’s fast. But two other things… First, I get a -1728 error if I don’t uncomment the ‘activate object’ line. It occurs when shifting to the second tab and the term ‘areas’ is highlighted.
Second, the OP did seem to have the requirement of displaying the results for rows and columns for each worksheet. Of course, he didn’t explain his purpose so it might actually be more useful to have lists. I did spend a fair amount of time bashing my head to figure out how to feed dialogues with excel object (which did lead me to the whole union thing so there was an upside).
Yeah, sorry, I commented that during testing and forgot to uncomment it.
Since the OP went with displaying column and row indices instead of addresses, I did the same. This is a bit slower than my original but should give the desired outcome.
use AppleScript version "2.4" -- Yosemite (10.10) or later
use framework "Foundation"
use scripting additions
(*
Returns an expanded range of integers from a start and a length
Given: {start, length}
Returns: {start, ... start + (length-1)}
Example: {3,7} -> {3,4,5,6,7,8,9}
*)
on expandRange:range
set {rangeStart, rangeLength} to range
if class of rangeStart is not integer then error "rangeStart must be an integer"
if class of rangeLength is not integer then error "rangeLength must be an integer"
set expandedRange to {rangeStart}
repeat rangeLength - 1 times
set rangeStart to rangeStart + 1
set end of expandedRange to rangeStart
end repeat
return expandedRange
end expandRange:
(*
Removes items in list1 that are also present in list2
*)
on removeItemsInListInOrder:list2 fromList:list1
set set2 to current application's NSSet's setWithArray:list2
set set1 to current application's NSMutableOrderedSet's orderedSetWithArray:list1
set1's minusSet:set2
return (set1's array()) as list
end removeItemsInListInOrder:fromList:
(*
Use NSListFormatter to get a nicely formatted list
*)
on formatList:aList
set formatter to current application's NSListFormatter's new()
set fmtList to formatter's stringFromItems:aList
return fmtList
end formatList:
on run
tell application "Microsoft Excel"
activate
set allSheets to name of every worksheet of active workbook
end tell
repeat with aSheet in allSheets
-- set things up
set colRanges to {}
set rowRanges to {}
tell application "Microsoft Excel"
activate object worksheet aSheet
tell worksheet aSheet
-- get a list of all column and row numbers on the sheet
set allColumns to (my expandRange:{1, count of columns of used range})
set allRows to (my expandRange:{1, count of rows of used range})
-- get the ranges of the visible columns by looking at row 1
set visibleCols to areas of (special cells (row 1 of used range of active sheet) type cell type visible)
repeat with colArea in visibleCols
set end of colRanges to {first column index of colArea, count of columns of colArea}
end repeat
-- get the ranges of the visible rows by looking at column 1
set visibleRows to areas of (special cells (column 1 of used range) type cell type visible)
repeat with rowArea in visibleRows
set end of rowRanges to {first row index of rowArea, count of rows of rowArea}
end repeat
end tell
end tell
set visibleColumns to {}
repeat with colRng in colRanges
set visibleColumns to visibleColumns & (my expandRange:colRng)
end repeat
set visibleRows to {}
repeat with rowRng in rowRanges
set visibleRows to visibleRows & (my expandRange:rowRng)
end repeat
set hiddenColumns to (my removeItemsInListInOrder:visibleColumns fromList:allColumns)
set hiddenrows to (my removeItemsInListInOrder:visibleRows fromList:allRows)
tell application "Microsoft Excel"
display dialog ("Worksheet " & aSheet & " has hidden" & return & ¬
"columns: " & (my formatList:hiddenColumns) as text) & return & ¬
"rows: " & (my formatList:hiddenrows) as text
end tell
end repeat
--(*
-- At this point, we have:
-- - a list of all column numbers
-- - a list of all row numbers
-- - a list of the ranges of the visible columns
-- - a list of the ranges of the visible rows
--
-- Now we need to turn the visible ranges into lists of column/row numbers
-- *)
--
--set visibleColumns to {}
--repeat with colRng in colRanges
-- set visibleColumns to visibleColumns & (my expandRange:colRng)
--end repeat
--
--set visibleRows to {}
--repeat with rowRng in rowRanges
-- set visibleRows to visibleRows & (my expandRange:rowRng)
--end repeat
--
--(*
-- Now that we have our four lists, we can just subtract the
-- visible columns/rows from all columns/rows to give us
-- the hidden columns/rows
-- *)
--set hiddenColumns to my removeItemsInListInOrder:visibleColumns fromList:allColumns
--set hiddenrows to my removeItemsInListInOrder:visibleRows fromList:allRows
--
--(*
-- We end up with list of indices for the hidden columns/rows
-- Do whatever you like with them
-- *)
end run
I’ve been chewing over the cells problem as I couldn’t help but imagine there had to be a better approach than boiling the ocean (of course, I was pleased that I found a solution of any sort). So I think I’ve come up with an ungainly and awkward solution but it is much faster, typically taking 2-3 seconds with a 300x300 range (per roosterboy’s solutions). I didn’t even try the previous solution on such a large table as I didn’t want it to become a boil my computer effort.
What this script does is construct a table congruent to the data’s and populate it with the ‘=cell’ formula, which is used to get each cell’s format, including if it’s ‘hidden’. After clearing the non-hidden format cells it’s left with a range object for the matching hidden cells, albeit they’re in the wrong table. Using ‘offset’, it gets the corresponding data table range object, clears the formats table and then does whatever. Currently, the script selects the cells and throws the range object up in a dialogue.
tell application "Microsoft Excel"
tell workbook 1
activate object worksheet 1
tell worksheet 1
-- data range should be "A2:KN300", "A1:KN1" can be headings or whatever
-- format range should be "LA2:WN300", "LA1:WN1" used for comparison
-- clear contents range "LA2:WN300" -- congruent to data range
set dr to range "A2:KN300"
set fr to range "LA2:WN300"
set hr to range "LA1:WN1"
-- fill format range with 'cell' formula
set formula of range "LA2" to "=CELL(\"format\",A2)"
fill down range "LA2:LA300"
fill right fr
-- replace formula with value
set value of fr to (get value of fr)
-- clear not "H" formats
-- set heading row for comparisons
set value of hr to "H"
set rc300 to range "LA:WN"
set r1 to column differences rc300 comparison hr
-- select r1
clear contents r1
clear contents hr
-- use not "", or "H"
-- set heading row for comparisons
set value of hr to ""
set r2 to column differences rc300 comparison hr
-- > range "[ten20.xlsx]rks!$LB$4:$LC$5,$WM$3:$WM$4,$LF$10:$LG$12,$LJ$294:$LK$295,$LN$294:$LO$295"
-- offset "H" range to data range
set o2 to (get offset r2 row offset 0 column offset -312)
delete column "LA:WN"
select o2
display dialog (get address o2) with title "Hidden cells of active sheet" giving up after 2
--> range "[ten20.xlsx]rks!$B$4:$C$5,$KM$3:$KM$4,$F$10:$G$12,$J$294:$K$295,$N$294:$O$295"
end tell
end tell
end tell
I imagine that there are numerous inefficiencies and it is clumsy to use up all that extra space, even if it’s temporary. I wonder if there isn’t some array operation that could speed it up. The slowest part seems to be filling the cell formula to all those cells. It has an unpleasant shortcoming in that while it seems to work reliably up to 400 rows, it begins to fail at some point thereafter. It does this even when the column count is low (e.g. 8). I don’t know why and maybe it’s only Excel 2011, or maybe I just botched something. It also needs to be generalized so that it can work automatically with varied data table sizes but that’s for another day.
Now, I will try with combined to show all alerts as single alert, but it won’t work. Let me know what I did wrong?
tell application "Microsoft Excel"
activate
set shAll to worksheets of active workbook
set hiddenColumnItems to {}
set hiddenColumnsSheet to {}
set hiddenRowItems to {}
set hiddenRowsSheet to {}
repeat with ss in shAll -- loop sheets
activate object ss
set nSht to name of ss
set cc to columns of used range of ss
repeat with c in cc -- loop columns
if hidden of c is true then
set fci to first column index of c
set the end of hiddenColumnItems to (fci as string)
set the end of hiddenColumnsSheet to nSht
--display dialog "Column " & (fci as text) & " of " & nSht with title "Hidden Columns"
end if
end repeat -- of columns
set rr to rows of used range of ss
repeat with r in rr -- loop rows
if hidden of r is true then
set fri to first row index of r
set the end of hiddenRowItems to (fri as string)
set the end of hiddenRowsSheet to nSht
--display dialog "Row " & (fri as text) & " of " & nSht with title "Hidden rows"
end if
end repeat -- of rows
end repeat -- of sheets
display dialog ("Column " & hiddenColumnItems) & " is missing in the " & hiddenColumnsSheet & " sheet." & ("
Row " & hiddenRowItems) & " is missing in the " & hiddenRowsSheet & " sheet."
end tell
set formula of range "LA2" to "=CELL(\"format\",A2)"
fill down range "LA2:LA300"
fill right fr
should be a wee bit faster as:
set formula of fr to "=CELL(\"format\",A2)"
Excel will fill in the formula automatically if you have more than one cell as the target range.
That solution looks pretty good! Definitely faster than the solution I came up with using NSArray and NSOrderedCollectionDifference, which still required looping through all columns of the sheet.
I want to alert all the hidden rows, columns in a single alert. I have tried with below code, but it not display the alert properly. Please review my below code and guide me how to show the hidden rows & columns in the single alert.
tell application "Microsoft Excel"
activate
set shAll to worksheets of active workbook
set hiddenColumnItems to {}
set hiddenColumnsSheet to {}
set hiddenRowItems to {}
set hiddenRowsSheet to {}
repeat with ss in shAll -- loop sheets
activate object ss
set nSht to name of ss
set cc to columns of used range of ss
repeat with c in cc -- loop columns
if hidden of c is true then
set fci to first column index of c
set the end of hiddenColumnItems to (fci as string)
set the end of hiddenColumnsSheet to nSht
--display dialog "Column " & (fci as text) & " of " & nSht with title "Hidden Columns"
end if
end repeat -- of columns
set rr to rows of used range of ss
repeat with r in rr -- loop rows
if hidden of r is true then
set fri to first row index of r
set the end of hiddenRowItems to (fri as string)
set the end of hiddenRowsSheet to nSht
--display dialog "Row " & (fri as text) & " of " & nSht with title "Hidden rows"
end if
end repeat -- of rows
end repeat -- of sheets
display dialog ("Column " & hiddenColumnItems) & " is missing in the " & hiddenColumnsSheet & " sheet." & ("
Row " & hiddenRowItems) & " is missing in the " & hiddenRowsSheet & " sheet."
end tell