asked on

Hiding Excel rows

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
or

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

or

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!

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.

Fernando

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

georgelesser

ASKER

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!!