Hiding Excel rows

Posted on 2006-04-24
Last Modified: 2012-08-13
I am using and excel automation.

I have an excel worksheet on which I want to hide a range of rows.

I calculate the start row J.  I know then end row is say 400.

When I try:

        Rng = XLSheet.Range("100:400")
        Rng.Hidden = True

       Rng = XLSheet.Range(j & ":400")
        Rng.Hidden = True


       Rng = XLSheet.Range("R100:R400")
        Rng.Hidden = True

I get:
Additional information: Unable to set the Hidden property of the Range class

Any help would be appreciated.

I know I can iterate through a loop as follows, I'm wondering if there is anything faster?
        For j = 25 To 452
            HideRng = XLBook.Worksheets(1).Rows(j)
            HideRng.Hidden = True
        Next j

Thanks so much!
Question by:georgelesser
    LVL 62

    Expert Comment

    by:Fernando Soto
    Hi georgelesser;

    I am teaching myself to use VB .Net to access Excel myself right now. The Range method uses the string passed in to it in the A1-style reference format. For example A1 or B21. The A is column A and the row is 1, or the column is B and the row is 21. So lets say you want the range of cells from the third column and first row to the 5 column and row 100 the string should be "C1:E100". C1 being the top left cell and E100 being the bottom right cell of the range. So from your example above, Rng = XLSheet.Range("100:400"), let say we are talking about the first two columns in the sheet it should be written as, Rng = XLSheet.Range("A100:B400"). And this would be the range of cells from column 1, A, starting at row 100 to column 2, B, ending at row 400.

    I hope that this is of some help.

    LVL 46

    Accepted Solution

    Hi georgelesser,

    Try using Rng.EntireRow.Hidden = True



    Author Comment

    Fernando's correct as far as defining a range.  However, I need to specify entire rows as part of my range in order to hide them.  I don't believe you can hide only a section of a worksheet (please correecxt me if I am wrong!).

    WebTubbs - your suggestion worked great!!!  I'm sure it's faster than looping through and hiding each row individually.

    Thanks again for the help!!


    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Introduction When many people think of the WebBrowser ( control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
    It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
    This video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now