+ Reply to Thread
Results 1 to 9 of 9

Naming Ranges

  1. 02-09-2012, 08:32 AM #1
    j_Southern is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Naming Ranges

    Hi,
    I like to define a dynamic range in the define name window as follows
    Please Login or Register  to view this content.
    This works fine as long as all the cells in col A and row 1 are filled.
    If this is not the case I have defined the range in VBA. using rows.count and columns.count

    I now have a situation where I would like to define a dynamic range which may easily have lots of empty cerlls using the define name window. I have not been able to find worksheet functions to do the job.
    Can anyone help please
    John
    Last edited by j_Southern; 02-13-2012 at 10:57 AM.
    Reply With QuoteRegister To Reply
  2. 02-09-2012, 08:46 AM #2
    Bob Phillips is offline
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Naming Ranges

    Try this

    =OFFSET($A$1,0,0,MATCH(REPT("z",99),$A:$A),MATCH(REPT("z",99),$1:$1))
    Reply With QuoteRegister To Reply
  3. 02-09-2012, 10:20 AM #3
    j_Southern is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Naming Ranges

    Hi,
    Thanks for your help, but that doesn't work.
    John
    Reply With QuoteRegister To Reply
  4. 02-09-2012, 10:24 AM #4
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Naming Ranges

    What kind of data do you have in Column A and Row 1?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.
    Reply With QuoteRegister To Reply
  5. 02-09-2012, 11:30 AM #5
    j_Southern is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Naming Ranges

    A mixture of strings and numbers and blanks
    John
    Reply With QuoteRegister To Reply
  6. 02-09-2012, 02:47 PM #6
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Naming Ranges

    Try:

    Please Login or Register  to view this content.
    This takes into account that it might be possible to have only text (and blanks) or only numbers (and blanks) too...
    Reply With QuoteRegister To Reply
  7. 02-10-2012, 08:27 AM #7
    Bob Phillips is offline
    Forum Expert Bob Phillips's Avatar
    Join Date
    09-03-2005
    Location
    Wessex
    MS-Off Ver
    Office 2003, 2010, 2013, 2016, 365
    Posts
    3,284

    Re: Naming Ranges

    Quote Originally Posted by j_Southern View Post
    A mixture of strings and numbers and blanks
    John
    =OFFSET($A$1,0,0,MAX(IFERROR(MATCH(99^99,$A:$A),0),IFERROR(MATCH(REPT("z",99),$A:$A),0)),MAX(IFERROR(MATCH(99^99,$1:$1),0),IFERROR(MATCH(REPT("z",99),$1:$1),0)))
    Reply With QuoteRegister To Reply
  8. 02-13-2012, 10:57 AM #8
    j_Southern is offline
    Forum Contributor
    Join Date
    03-30-2010
    Location
    Manchester England
    MS-Off Ver
    Excel 2010
    Posts
    992

    Re: Naming Ranges

    Thank you all for your help. Both methods work. I shall use the shorter one.
    It does seem odd that there isn't a function like VBA CurrentRegion which would make it easier to name a range containing blank cells..
    John
    Reply With QuoteRegister To Reply
  9. 02-13-2012, 11:07 AM #9
    NBVC is offline
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Naming Ranges

    Though note that shorter isn't always most ideal.. some functions are Volatile....
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1