Range Resize problem

[hooroy]

I have a one-cell range named "FirstRow" in a sheet named "Reports". The cell is A22. I want to resize the range to be 10 rows (still one column). The following code line produces the error shown below:

Sheets("Reports").Range("FirstRow").Resize (10)

Run-time error '438':
Object doesn't support this property or method

Same error occurs with:
Sheets("Reports").Range("A22").Resize (10)

I tried on Excel 2007 w/ Vista and Excel 2010 w/ Win 7 -- same result

Why isn't this working? Thanks in advance for any help.

hooroy

 

[Peter_SSs]

Welcome to the MrExcel board!

What do you want to do with the expanded range? For a trivial example, try

Sheets("Reports").Range("FirstRow").Resize(10).Select

 

[hooroy]

Thanks, Peter. All I'm trying to do at this point is EXPAND (resize) the range. When I add ".Select" as you suggest, all 10 cells (A22:A31) are selected, but the range name "FirstRow" still only relates to the original one-cell range. I want to resize "FirstRow" to encompass A22:A31, i.e., 10 cells.
hooroy

 

[Peter_SSs]

OK, if you are trying to change the range of the named range, then try this.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> ChangeNamedRangeRange()<br>    <SPAN style="color:#00007F">Dim</SPAN> s <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><br>    <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.Names("FirstRow")<br>        s = .RefersTo<br>        s = Left(s, InStr(1, s, "!")) _<br>            & .RefersToRange.Resize(10).Address<br>        .RefersTo = s<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[hooroy]

Works like a charm, Peter. Thanks for your help. The necessity for all that code sure is counter to my understanding of what .resize is supposed to do. I've got a lot to learn about .resize.
hooroy