Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Simply use:

    =OFFSET(rngBig,0,0,COUNTA(Sheet1!$A:$A),1)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #5
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #10
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    You might do well with a copy of my Name Manager
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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. #13
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

    Alan

  14. #14
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #15
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    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 LastLast

Posting Permissions

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