+ Reply to Thread
Results 1 to 2 of 2

Naming a dynamic range

  1. #1
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    966

    Naming a dynamic range

    I have a dynamic range such as that in the attached worksheet, which I need to name.
    In this situation I usually use offset and counta in a formulation like Offset(G7,0,0,CountA($G:$G),CountA($7:$7)) in the Define Name feature.

    This works because the first row and column always are full and start at a given cell (G7)

    Unfortunately this will not be true in my present problem. It is the last row and first column which will always be full, but as the range is dynamic I don't know the position of the last row.
    I have tried using Offset with the counta of the first column to fix the position of the last row so I can count the columns but I have got in a muddle.
    Can any one help with this approach, or perhaps suggest a better way ?

    John
    Attached Files Attached Files

  2. #2
    Forum Expert GeneralDisarray's Avatar
    Join Date
    09-15-2011
    Location
    Pittsburgh, PA, USA
    MS-Off Ver
    Windows Excel 2007/2010/2013
    Posts
    1,416

    Re: Naming a dynamic range

    Do you at least have a known header row location? Can't you just count the header row for width?

    EDIT:

    This will do it as it currently sits:

    =Sheet1!$G$7:INDEX(Sheet1!$G$7:$N$1048576,COUNTA(Sheet1!$G$7:$G$1048576),COUNTA(Sheet1!$G$7:$XFD$7))


    OR - if you move the dynamic range to the upper left corner of the sheet, it's a bit simpler:

    =Sheet1!$A$1:INDEX(Sheet1!$1:$1048576,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))
    Last edited by GeneralDisarray; 10-26-2015 at 11:23 AM.
    Remember, saying thanks only takes a second or two. Click the star icon(*) below the post you liked, to give some Rep if you think an answer deserves it.

    Please,mark your thread [SOLVED] if you received your answer.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Dynamic file naming in VBA
    By dperry in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-04-2011, 02:22 PM
  2. Rows.Count error in dynamic range naming
    By Andrew-Mark in forum Excel Programming / VBA / Macros
    Replies: 21
    Last Post: 04-07-2010, 03:27 AM
  3. dynamic naming of cells
    By gazerbeam in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-30-2008, 04:21 PM
  4. dynamic naming cells
    By excelsmu in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-31-2007, 01:08 PM
  5. Naming a dynamic range
    By matpj in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-02-2006, 09:24 PM
  6. Dynamic naming of range needed
    By XXL User in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-03-2006, 03:26 PM
  7. Dynamic Range Naming for VBA
    By mrtoemoss@hotmail.com in forum Excel General
    Replies: 2
    Last Post: 04-24-2005, 12:06 AM
  8. [SOLVED] Dynamic range naming in VB6.3 for Excel
    By Paul Chapman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-05-2005, 11:07 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1