Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    CurrentRegion (2000)

    Is there an Excel Function which returns the equivalent of vba CurrentRegion?

  2. #2
    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: CurrentRegion (2000)

    There is no builtin function.

    I also do not think it is possible to create a UDF to do this. The currentregion property works with excel in a way that is not "allowed" for worksheet functions (functions which excel uses to get values in range are more limited and can not format, insert, delete, select, etc. CurrentRegion Property involves something akin to "selecting")

    It can be done with a VB function, but that does not help if you need a worksheet function.

    What are you trying to do? Perhaps there is another way...
    Steve

  3. #3
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CurrentRegion (2000)

    Steve,
    That's kind of what I expected. I came up with the following work-around:

    I have several tables arranged horizontally across a worksheet named Tables, with headers in row 1
    and the upper left cell of data in each table Named "xxxTblOne".

    Each table is a vertically dynamic Named Range:
    eg ResTbl = OFFSET(ResTblOne,0,0,COUNTA(Tables!$A:$A)-1,5)

    Periodically I need to insert an additional field or column in a table. I was looking for a way to make the
    Tbl range horizontally dynamic as well ( changing the 5 in this example to a dynamic width).
    Since there are several tables on the sheet I could not just use CountA(Tables!$1:$1)
    I was looking for a way to use the CurrentRegion of the UpperLeft cell of the table.

    By separating the tables with blank columns and using vba with the Wbk_Open event I
    can redefine the Tbl names both horiz and vertically , without manually changing
    the width in the Name definition.

    <pre>ActiveWorkbook.Names.Add Name:="ResTblWidth", RefersTo:= _
    Range("ResTblOne").CurrentRegion.Columns.Count
    ActiveWorkbook.Names.Add Name:="ResTbl", RefersTo:= _
    "=OFFSET(ResTblOne,0,0,COUNTA(Tables!$A:$A)-1,ResTblWidth)"
    </pre>


  4. #4
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CurrentRegion (2000)

    I've come up with a shorter solution:

    Insert a blank row between the headers and the data. (this would be Row 2)

    Then just redefine each similar to:

    ActiveWorkbook.Names.Add Name:="ResTbl", RefersTo:= _
    Range("ResTbl").CurrentRegion

  5. #5
    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: CurrentRegion (2000)

    Other options:

    Just put each dynamic table on a separate worksheet. Then you can use COUNTA to get both vertical and horizontal dimension.

    Do not use dynamic ranges but have a definite start and end column for each. Then if you want to add new rows or columns, insert before the last column/row and the ranges will automatically be expanded.

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Feb 2003
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: CurrentRegion (2000)

    I'll look at these.

    Also, I found that my 2nd (shorter solution- inserting row 2) destroyed my Advanced Filter since it apparently needs the col headers directly above the data in the database.

Posting Permissions

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