Sunday, June 13, 2021

#1 2021-06-10 07:22:27 am

Vijay_Yukthi
Member
Registered: 2021-04-16
Posts: 8

Applescript code to find the hidden rows, columns or cell in the Excel

Hi all,

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.

Applescript:

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

Last edited by Vijay_Yukthi (2021-06-11 04:56:30 am)

Offline

 

#2 2021-06-11 02:35:21 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 40

Re: Applescript code to find the hidden rows, columns or cell in the Excel

I posted this elsewhere but I see this question here now as well, so….

The key is

activate object

.

Applescript:

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

Offline

 

#3 Yesterday 01:39:17 am

Vijay_Yukthi
Member
Registered: 2021-04-16
Posts: 8

Re: Applescript code to find the hidden rows, columns or cell in the Excel

Thanks! Its great, the code is working fine.

Is it possible to find the hidden cell in the sheets of excel?

Offline

 

#4 Yesterday 03:58:27 am

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 40

Re: Applescript code to find the hidden rows, columns or cell in the Excel

Not really sure what you mean by hidden cells.

Offline

 

#5 Yesterday 04:26:18 am

Vijay_Yukthi
Member
Registered: 2021-04-16
Posts: 8

Re: Applescript code to find the hidden rows, columns or cell in the Excel

if any junk contents hide in the cells of rows/columns

Last edited by Vijay_Yukthi (Yesterday 04:26:43 am)

Offline

 

#6 Yesterday 08:39:00 am

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 40

Re: Applescript code to find the hidden rows, columns or cell in the Excel

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.

Applescript:

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.


Filed under: excel

Offline

 

#7 Yesterday 12:11:04 pm

roosterboy
Member
Registered: 2010-04-19
Posts: 46

Re: Applescript code to find the hidden rows, columns or cell in the Excel

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.



Huh, never encountered that before. Good to know.

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.

Thanks!

Offline

 

#8 Yesterday 03:43:19 pm

roosterboy
Member
Registered: 2010-04-19
Posts: 46

Re: Applescript code to find the hidden rows, columns or cell in the Excel

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.

Applescript:


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.

Last edited by roosterboy (Yesterday 03:47:30 pm)

Offline

 

#9 Yesterday 05:42:20 pm

Mockman
Member
From:: Toronto
Registered: 2020-05-27
Posts: 40

Re: Applescript code to find the hidden rows, columns or cell in the Excel

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).

Offline

 

#10 Yesterday 08:23:35 pm

roosterboy
Member
Registered: 2010-04-19
Posts: 46

Re: Applescript code to find the hidden rows, columns or cell in the Excel

Mockman wrote:

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.



Yeah, sorry, I commented that during testing and forgot to uncomment it.

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).



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.

Applescript:


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

Offline

 

Board footer

Powered by FluxBB

RSS (new topics) RSS (active topics)