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.

Detecting Hidden Rows

by Allen Wyatt
(last updated June 21, 2018)

7

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.

Note:

If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He  is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Converting Text Into a Table

You can easily convert regular text into a table using a couple of different methods. This tip explains how to make the ...

Discover More

Determining the Horizontal Position of the Insertion Point

Need to figure out how far the insertion point is from the left margin? You can do so by using this small macro that ...

Discover More

Changing Outline Structure

When working with the outline of a document, you can easily move whole sections of your document. It is as easy as ...

Discover More

Program Successfully in Excel! John Walkenbach's name is synonymous with excellence in deciphering complex technical topics. With this comprehensive guide, "Mr. Spreadsheet" shows how to maximize your Excel experience using professional spreadsheet application development tips from his own personal bookshelf. Check out Excel 2013 Power Programming with VBA today!

More ExcelTips (menu)

Hiding and Unhiding Rows

When building a worksheet, you may need to hide some of the rows or unhide other, previously hidden, rows. It's easy to ...

Discover More

Setting Row Height

When you enter information into a row on a worksheet, Excel automatically adjusts the height of the row based on what you ...

Discover More

Adjusting Row Height for a Number of Worksheets

Adjusting the height of a row or range of rows is relatively easy in Excel. How do you adjust the height of those same ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is seven minus 6?

2014-12-04 06:43:03

SACHIN

I HAVE TRIED THIS ON EXCEL 2013 AND IT DOES NOT WORK!!


2012-12-20 06:21:08

Dave K

Peter,

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

Dave


2012-12-19 18:22:09

Peter Jagaraj

Will this tip tried out in Excel 2010?


2012-10-22 03:52:50

Dave K

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


2012-10-21 08:00:50

David Grain

shopkins is right with small spreadsheets but I am currently working with a spreadsheet with nearly 2000 rows and 40 columns


2012-10-20 07:59:17

shopkins

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.


2012-10-20 05:14:47

David Grain

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.


This Site

Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.