Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Use current region in dynamic named range (Excel 2003)

    Hi everybody:

    I have a database application that needs to read in data updated from one area of several workbooks used by various people, each with a different number of rows in it that will change as they work. I'd like to create a dynamic, named range that I can use in my Excel code to trap when users add a new row within the range. It could also be used to get the correct area from each workbook each time it is updated.

    I cannot use COUNTA, because the users have several tables underneath the one I need that will have data in them. And no, I can't get them to move the tables out of the way.

    Is it possible to use Excel's currentregion property to return the size of the range? I've tried the following but get an error.

    =offset($G$6,0,0,Range($G$6).currentregion.rows.co unt,-6)

    Thank you, in advance, for your help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use current region in dynamic named range (Excel 2003)

    CurrentRegion.Rows.Count is a VBA expression, not an Excel worksheet function, so you cannot use it in a formula. I'd put the tables that you need in a sheet of their own.

  3. #3
    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: Use current region in dynamic named range (Excel 2003)

    If you are going to use this range in macro code, why not just use the "current region" or some range derived from the current region, at runtime and not bother naming it? You could at runtime just set at a variable (DIMmed as a range) to be used in your code instead of calling a name.

    Steve

  4. #4
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use current region in dynamic named range (Excel 2003)

    Thanks to both of you.

    But, I cannot move the tables - these are not regular database front-ends, but users' working spreadsheets that have multiple links to and from other workbooks, and cannot be manipulated by me, unfortunately. (No, it wasn't my idea!)

    And Steve, I can do as you suggest when downloading the data at runtime, but it doesn't solve my need for my Excel code to be able to recognize the expansion of the import data range while the users are changing the data. I'm trapping the addition of rows by putting a value in a hidden column and using COUNTBLANK on the range to trigger a new, blank cell in the added row. So, the range needs to be dynamic, as far as I can tell.

    Any other ideas?

    Thank you both for your help.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use current region in dynamic named range (Excel 2003)

    Instead of importing a named range, you'll probably have to use Automation to loop through the rows of Range("G6").CurrentRegion and add them to the table one by one.

  6. #6
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use current region in dynamic named range (Excel 2003)

    Thanks, Hans. But that doesn't solve my problem, because I need to trigger the insert row action in order to hit my database and insert a new ID number into the spreadsheet.

    Any other ideas?

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Use current region in dynamic named range (Excel 2003)

    Perhaps you could use the Worksheet_Change event, but I don't think it is a good idea to insert and delete records in the database dynamically while a user is pottering around in the worksheet.

  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: Use current region in dynamic named range (Excel 2003)

    What about defining a "border range"? Insert a row on top of your range, and underneath your range and a column to the left and to the right of your data. Then define the name as containing this entire range of the hidden border. Fill the range with some value or text (so thye are nto blank and will not be counted) and color the range some.

    It does not have to be dynamic since inserting/deleting rows/columns will expand/contract the range...

    Steve

  9. #9
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Piscataway, New Jersey, USA
    Posts
    171
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Use current region in dynamic named range (Excel 2003)

    I did that. I'll work around the situation. Thanks, again, for your input.

Posting Permissions

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