Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Detecting Hidden Rows

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Detecting Hidden Rows.

Jesse has a large worksheet that may contain hidden rows. He wonders if there is a way to find out if there are hidden rows in the worksheet other than by looking down the many rows to see what's missing. If he unhides all the hidden rows, he still won't be able to tell what, if any, rows may have been hidden.

One way you can identify hidden rows is to follow these general steps:

  1. In a column that has nothing in it, select all the cells that will cover the area you want to check. (You can select the entire column, if you desire, but that may be overkill.)
  2. Press Alt+; (that's a semicolon). Excel selects only the unhidden cells in the selected range.
  3. Press X (or some other viewable character) and press Ctrl+Enter. This puts the character (X) into all the visible cells.

Unhide all the rows, and you'll be able to easily see which cells in that column don't have the character (X) in them. These are the rows that were previously hidden. You could also, if desired, use the same general approach, but after step 2 (instead of step 3) you could apply some pattern or color to the cells. Once you unhide all the rows, those cells without any pattern or color are the ones that were previously in hidden rows.

If you don't want to unhide rows at all, perhaps the best way to find out the information is to use a macro. The following simple macro steps through the first 1,000 rows of a worksheet and then lists, in a message box, the rows that are hidden.

Sub ShowRows()
    Dim rng As Range
    Dim r As Range
    Dim sTemp As String

    Set rng = Range("A1:A1000")
    sTemp = ""
    For Each r In rng.Rows
        If r.EntireRow.Hidden Then
            sTemp = sTemp & "Row " & Mid(r.Address, 4) & vbCrLf
        End If
    Next r

    If sTemp > "" Then
        sTemp = "The following rows are hidden:" & vbCrLf & _
          vbCrLf & sTemp
        MsgBox sTemp
    Else
        MsgBox "There are no hidden rows"
    End If
End Sub

Note that the heart of the macro—where it determines whether a row is hidden or not—is in checking the Hidden property of the EntireRow object. If this property is True, then the row is hidden.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12216) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Detecting Hidden Rows.

Related Tips:

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

SACHIN    04 Dec 2014, 06:43
I HAVE TRIED THIS ON EXCEL 2013 AND IT DOES NOT WORK!!
Dave K    20 Dec 2012, 06:21
Peter,

I have just tried my code on Excel 2010 and it works fine. I imagine that Allen's version does too.

Dave
Peter Jagaraj    19 Dec 2012, 18:22
Will this tip tried out in Excel 2010?
Dave K    22 Oct 2012, 03:52
This works well, but when you click OK on the message box, you lose the list. An alternative is to write the row numbers to a new worksheet, giving you a "permanent" record. Here is my suggestion, based on sheet names "sheet1" and "sheet2". Select the rows that you want to check and run the macro.

Sub CheckRowHeight()
    Dim r As Range
    Dim i As Integer
    Dim RN As Long ' row number
    
    i = 1
    Worksheets("sheet2").Range("A" & i).Value = "Hidden Rows"
    i = i + 1
    
    Worksheets("sheet1").Activate
    
    For Each r In Selection.Rows
        If r.RowHeight = 0 Then
            RN = r.Row
            Worksheets("sheet2").Range("A" & i).Value = RN
            i = i + 1
        End If
    Next r
    Worksheets("sheet2").Activate
    Range("C2").Value = (i - 2) & " hidden rows found"
    Range("C3").Select
End Sub
David Grain    21 Oct 2012, 08:00
shopkins is right with small spreadsheets but I am currently working with a spreadsheet with nearly 2000 rows and 40 columns
shopkins    20 Oct 2012, 07:59
Wouldn't it be easier to simply look at the column letters at the top of the spreadsheet ?? Missing letters mean the column is hidden.
David Grain    20 Oct 2012, 05:14
In one company that I worked for we had a firm rules not to use hidden rows or columns. We used the Group and Ungroup facility instead.

I have taken this rule with me to other companies for which I have worked.
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.