# Thread: Dynamic range within named range (2000 sp-3/ 98SE)

1. ## Dynamic range within named range (2000 sp-3/ 98SE)

I'm wanting to define a dynamic range (rngDyn) as a portion of a larger named range (rngBig), so that as "rows" are added to rngBig, rngDyn will grow accordingly. I'm wanting to use a worksheet function of the type:
=OFFSET(Sheet1!\$A\$1,0,0,COUNTA(Sheet1!\$A:\$A),1)

to define rngDyn, but using rngBig as the base range reference. I'm having difficulty getting the syntax correct, both as a worksheet formula and in VBA code. Any tips appreciated.

Alan

2. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

Simply use:

=OFFSET(rngBig,0,0,COUNTA(Sheet1!\$A:\$A),1)

3. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

Thanks Jan, but is the COUNTA(Sheet1!\$A:\$A) correct? I'm really needing to apply COUNTA method to the equivalent of rngBig!\$A:\$A, but that syntax is not correct of course. The final resultant range I need, with rngBig in its present form as shown, is B4:B7. I think I'd also have to start with rngBig,1,0

Alan

4. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

How about:
=OFFSET(rngBig,1,0,COUNTA(INDEX(rngBig,0,1))-1,1)

Steve

5. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

Thanks Steve... looks like it does the trick. I'll have to read up on using INDEX. Is there a way to have this name appear in the Name Box as well?

Alan

6. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

If you mean the name box in the formula bar: no, dynamic ranges are never displayed in the name box.

7. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

Thanks Hans. That answers that one. It's probably desirable to have it hidden from the user in this case anyway.

Alan

8. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

People typically use INDEX to get a single point. Index actually results in a range of values:

<pre>Index(rng,2,3)</pre>

is a "range" consisting of a single point. It is the 2nd row from the top and the 3rd column to the right from the upper left corner of rng.

<pre>Index(rng,2,0)</pre>

is a range consisting of the 2nd row from the top and ALL the columns

<pre>Index(rng,0,3)</pre>

is a range consisting of the 3rd column to the right and ALL the rows

Steve
PS I thought of this for rngDyn (simpler than using INDEX):

<pre>=OFFSET(rngBig,1,0,ROWS(rngBig)-1,1)</pre>

9. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

You can also use VB to hide the name so it is not even visible in the Insert - name dialog:

<pre> ActiveWorkbook.Names.Add Name:="rngDyn", _
RefersTo:="=OFFSET(rngBig,1,0,ROWS(rngBig)-1,1)", _
Visible:=False</pre>

Steve

10. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

Thanks for both tips Steve. I'm sure they'll be useful in the context in which I'm coding (and learning!) In fact, the zero (referring to all rows/ columns) threw me until I read the "fine print" in Help.

cheers
Alan

11. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

You might do well with a copy of my Name Manager

12. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

I think it is a "neat trick". You can name an entire range, than use index in formulas to refer to any particular column or row in it without having to name all the separate parts of the "big range". It saves making lots of names on occasions.

You can even just have the big range "dyanamic" with offset, and use index to define the name of "subsection" cols and/or rows if desired, instead of using offset multiple times to count rows/columns, etc.

Steve

13. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

Thanks Jan. I'll check that out as well.

Alan

14. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

Yes, it is very "neat" Steve. I'm actually using it to populate list/ combo boxes with the smaller dynamic range values that come from the bigger tables. I've reused the following procedure:
<code>
Sub FillCombo(cbx As ComboBox, rngName As String)
Dim rng As Range, i As Integer
Set rng = ThisWorkbook.Names(rngName).RefersToRange

With cbx
For i = 1 To rng.Rows.Count
.AddItem rng.Cells(i, 1).Value
Next i
.ListIndex = 0
End With

End Sub
</code>
for about a dozen form dropdowns, with just simple calls like:
<code>
FillCombo ComboBox8, "_Branch"
</code>
Very handy indeed.

Alan

15. ## Re: Dynamic range within named range (2000 sp-3/ 98SE)

If you use the combobox from the FORMS toolbar, you can use the name itself as the "fillrange" and not require any coding. It will dynamically change...

Steve

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•