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.

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

Thanks! Its great, the code is working fine.

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

Not really sure what you mean by hidden cells.

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

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.

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!

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.

Thanks guys for your replies!

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

This:


           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.

Hi guys,

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