Hiding Excel rows

Posted on 2006-04-24
Medium Priority
Last Modified: 2012-08-13
I am using vb.net 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 64

Expert Comment

by:Fernando Soto
ID: 16531461
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 48

Accepted Solution

Wayne Taylor (webtubbs) earned 2000 total points
ID: 16531526
Hi georgelesser,

Try using Rng.EntireRow.Hidden = True



Author Comment

ID: 16535679
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

616 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