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 25, 2015)

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.

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

Selecting a Range of Cells Relative to the Current Cell

When processing information in a macro, you often need to select different cells relative to the currently selected cells. ...

Discover More

Understanding Master and Subdocuments

Most people use Word to create regular documents that you edit, view, and print. The program also allows you to create a ...

Discover More

Getting Rid of Manual Paragraph Numbering

Word includes a tool that allows you to easily apply automatic numbering to your paragraphs. You may be editing a document ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

MORE EXCELTIPS (MENU)

Automatic Row Height For Merged Cells with Text Wrap

When you have text wrap turned on in a cell, Excel expands the height of the row as you add more text to the cell. When you ...

Discover More

Hiding Rows Based on Two Values

It's easy to use filtering to hide rows based on the value in a cell, but how do you hide rows based on the values in two ...

Discover More

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 do; ...

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 for this tip:

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. 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 9 + 8?

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.


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.

Links and Sharing
Share