Indentify hidden rows & columns

R

R J Solanki

New Member
Joined
Jun 22, 2011
Messages
44
Hi,
Is there any way to find out how many & which rows & columns are hidden

Thanks in advance
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
Sort by date Sort by votes
R J Solanki said:
Hi,
Is there any way to find out how many & which rows & columns are hidden

Thanks in advance
Click to expand...
Depending on whether you are just looking within the sheet's used range or whether you need to check the whole sheet, you could try one of these.

If you might have large blocks of consecutive hidden rows/columns then there may be a better way to present the results. Post back if this is the case.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> HiddenRowsColumnsUsedRange()<br>    <SPAN style="color:#00007F">Dim</SPAN> rH <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, cH <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> f <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, l <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">With</SPAN> ActiveSheet.UsedRange<br>         f = .Row<br>         l = f + .Rows.Count - 1<br>         <SPAN style="color:#00007F">For</SPAN> i = f <SPAN style="color:#00007F">To</SPAN> l<br>            <SPAN style="color:#00007F">If</SPAN> Rows(i).Hidden <SPAN style="color:#00007F">Then</SPAN><br>                rH = rH & ", " & i<br>                r = r + 1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>         <SPAN style="color:#00007F">Next</SPAN> i<br>         <SPAN style="color:#00007F">If</SPAN> rH <> "" <SPAN style="color:#00007F">Then</SPAN><br>            rH = Replace(rH, ", ", "", 1, 1)<br>         <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>         <br>         f = .Column<br>         l = f + .Columns.Count - 1<br>         <SPAN style="color:#00007F">For</SPAN> i = f <SPAN style="color:#00007F">To</SPAN> l<br>            <SPAN style="color:#00007F">If</SPAN> Columns(i).Hidden <SPAN style="color:#00007F">Then</SPAN><br>                cH = cH & ", " & Replace(Cells(1, i).Address(0, 0), 1, "")<br>                c = c + 1<br>            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>         <SPAN style="color:#00007F">Next</SPAN> i<br>         <SPAN style="color:#00007F">If</SPAN> cH <> "" <SPAN style="color:#00007F">Then</SPAN><br>            cH = Replace(cH, ", ", "", 1, 1)<br>         <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    MsgBox "Rows hidden (" & r & "): " & rH & vbLf & _<br>        "Columns hidden (" & c & "): " & cH<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><br><SPAN style="color:#00007F">Sub</SPAN> HiddenRowsColumnsWholeSheet()<br>    <SPAN style="color:#00007F">Dim</SPAN> rH <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, cH <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, r <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, c <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Rows.Count<br>       <SPAN style="color:#00007F">If</SPAN> Rows(i).Hidden <SPAN style="color:#00007F">Then</SPAN><br>           rH = rH & ", " & i<br>           r = r + 1<br>       <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">If</SPAN> rH <> "" <SPAN style="color:#00007F">Then</SPAN><br>       rH = Replace(rH, ", ", "", 1, 1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <br>    <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> Columns.Count<br>       <SPAN style="color:#00007F">If</SPAN> Columns(i).Hidden <SPAN style="color:#00007F">Then</SPAN><br>           cH = cH & ", " & Replace(Cells(1, i).Address(0, 0), 1, "")<br>           c = c + 1<br>       <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    <SPAN style="color:#00007F">If</SPAN> cH <> "" <SPAN style="color:#00007F">Then</SPAN><br>       cH = Replace(cH, ", ", "", 1, 1)<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>         <br>    MsgBox "Rows hidden (" & r & "): " & rH & vbLf & _<br>        "Columns hidden (" & c & "): " & cH<br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
 
Upvote 0
Your problem got me to thinking - so here's an alternate solution from Peter's. Mine is a lot faster (at running the whole sheet), but Peter's are more robust. Mine may go flaky if UsedRange isn't reset or if all row or all columns are filled or if any of the operations result in ranges with more than 2,048 areas; whereas Peter's are pretty bulletproof.

Code: 
Sub ListHiddenRowsAndColumns()
    Dim rngBlankCol     As Excel.Range, _
        rngBlankRow     As Excel.Range, _
        rngHidden       As Excel.Range, _
        rngUsed         As Excel.Range, _
        rngVis          As Excel.Range, _
        strAddress      As String, _
        strColLetter    As String, _
        strMsg          As String, _
        wf              As Excel.WorksheetFunction
        
    Application.ScreenUpdating = False
    Set rngUsed = ActiveSheet.UsedRange
    Set rngBlankCol = Range("A1").Offset(, rngUsed.Columns.Count + 2).Resize(, 1).EntireColumn
    Set rngBlankRow = Range("A1").Offset(rngUsed.Rows.Count + 2).Resize(1).EntireRow
    
    '// count hidden columns
    On Error Resume Next
    Set rngVis = rngBlankRow.SpecialCells(xlCellTypeVisible)
    
    If rngVis.Count = rngBlankRow.Cells.Count Then
        strMsg = "Hidden Columns: none" & vbCr & vbCr
    Else
        rngBlankRow.Formula = "1"
        rngVis.Clear
        Set rngHidden = rngBlankRow.SpecialCells(xlCellTypeConstants, XlSpecialCellsValue.xlNumbers)
        Let strAddress = rngHidden.Address(0, 0)
        Let strAddress = Replace(strAddress, CStr(rngBlankRow.Row), "")
        Let strMsg = "Hidden Columns: (" & rngHidden.Count & ")  " & strAddress & vbCr & vbCr
    End If
    rngBlankRow.Clear
    
    '// count hidden rows
    On Error Resume Next
    Set rngVis = rngBlankCol.SpecialCells(xlCellTypeVisible)
    
    If rngVis.Count = rngBlankCol.Cells.Count Then
        strMsg = strMsg & "Hidden Rows: none"
    Else
        rngBlankCol.Formula = "1"
        rngVis.Clear
        Set rngHidden = rngBlankCol.SpecialCells(xlCellTypeConstants, XlSpecialCellsValue.xlNumbers)
        Let strAddress = rngHidden.Address(0, 0)
        Let strColLetter = rngBlankCol.Address
        Let strColLetter = Replace(strColLetter, "$", "")
        Let strColLetter = Left(strColLetter, InStr(1, strColLetter, ":") - 1)
        Let strAddress = Replace(strAddress, strColLetter, "")
        Let strMsg = strMsg & "Hidden Rows: (" & rngHidden.Count & ")  " & strAddress & vbCr & vbCr
    End If
    rngBlankCol.Clear
    
    MsgBox strMsg, vbInformation
End Sub
 
Last edited:
Upvote 0
I did a little more testing and it appears that USEDRANGE will always include hidden rows and columns, even if there are entirely blank rows or columns above or to the left of the hidden cells. So for me Peter's first routine is much faster unless the hidden rows are 100,000 rows or more down the worksheet.
 
Upvote 0
Greg Truby said:
I did a little more testing and it appears that USEDRANGE will always include hidden rows and columns, even if there are entirely blank rows or columns above or to the left of the hidden cells.
Click to expand...
Greg

I think that depends on whether the hidden row/column contains data. For example, take a completely fresh worksheet and enter data in E2:G4
Checking UsedRange reveals E2:G4 as expected
Hide column B
Checking UsedRange still reveals E2:G4. That is, the hidden column is not included.


I also think your code may destroy data. Take another fresh worksheet and again populate E2:G4. Hide row 1 and run your code. The data in column F is removed.

The problem is the assumption that UsedRange starts in cell A1 (which of course is usually the case). I think it could be overcome by swapping the red for the blue.
Rich (BB code): 
Set rngBlankCol = Range("A1").Offset(...
Set rngBlankCol = rngUsed.Cells(1, 1).Offset(...

Set rngBlankRow = Range("A1").Offset(...
Set rngBlankRow = rngUsed.Cells(1, 1).Offset(...
 
Upvote 0
Appears to me that hidden rows extend the used range, and hidden columns do not.
 
Upvote 0
shg said:
Appears to me that hidden rows extend the used range, and hidden columns do not.
Click to expand...
Yes, that does appear to be the case. Silly of me to assume rows would behave the same as columns! :eeek:
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Incrementing IP Addresses with hidden rows
Replies
2
Views
230
Tetra201
Tetra201
H
  • Solved
Copy hidden row and paste unhidden after last row
Replies
6
Views
110
Peter_SSs
Peter_SSs
gheyman
  • Question Question
Identify hidden rows when using a slicer
Replies
3
Views
275
RoryA
RoryA
W
  • Question Question
Unhide 'X' Amount of Columns from last hidden
Replies
2
Views
70
mumps
mumps
V
  • Question Question
Lock unhide sheet in a workbook with a password
Replies
2
Views
166
offthelip
O

Forum statistics

Threads
1,224,574
Messages
6,179,629
Members
452,933
Latest member
patv

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top