+ Reply to Thread
Results 1 to 12 of 12

dynamic shifting range, always at bottom of column

  1. #1
    Registered User
    Join Date
    08-10-2009
    Location
    Fort Collins, Colorado
    MS-Off Ver
    Excel 2003
    Posts
    29

    dynamic shifting range, always at bottom of column

    Hi, I was wondering if there was a way to create a two cell dynamic range that doesn't expand, but instead shifts.

    The cells that I'm interested in are always at the bottom of the column of data. For example, the first two cells I want as my range are C13 and D13. Then, for my next use of the range, I would like it to include C14 and D14. Is there a way to do this?

  2. #2
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: dynamic shifting range, always at bottom of column

    Dynamic Named Range for shifting rows

    =OFFSET(INDIRECT(ADDRESS(COUNTA(Sheet1!C:C),3)),-1,0,2,2)

  3. #3
    Registered User
    Join Date
    08-10-2009
    Location
    Fort Collins, Colorado
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: dynamic shifting range, always at bottom of column

    That looks great but I'm having a problem using that later on when I reference it here:

    Please Login or Register  to view this content.
    I am receiving the error "Application-defined or object-defined error" and I cannot for the life of me figure out why. Am I missing something???

    Thanks

  4. #4
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: dynamic shifting range, always at bottom of column

    What is .Values?
    Entia non sunt multiplicanda sine necessitate

  5. #5
    Registered User
    Join Date
    08-10-2009
    Location
    Fort Collins, Colorado
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: dynamic shifting range, always at bottom of column

    .values relates to chart creation, it establishes values for the chart. I don't think that the chart is the issue though and that is why I posted this thread in programming.

    When I use a range that I simply define as the same two cells always, it works fine. It's just when I tried the suggested dynamic range that it failed.

    Here's the code that I'm using and I'll put an asterisk next to the line that's giving me fits. The dynamic range on the worksheet is exactly as the earlier person suggested.

    Please Login or Register  to view this content.

  6. #6
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: dynamic shifting range, always at bottom of column

    So set a breakpoint in the code on that line, and when the code breaks, see if the range is correct. In the Immediate window, enter

    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    08-10-2009
    Location
    Fort Collins, Colorado
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: dynamic shifting range, always at bottom of column

    I think the range must be wrong because when I type that, I receive the same error.

    Is there another way to code that dynamic range?

    Thanks

  8. #8
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: dynamic shifting range, always at bottom of column

    There are, but how to depends on how the data is laid out. Post a workbook.

  9. #9
    Registered User
    Join Date
    08-10-2009
    Location
    Fort Collins, Colorado
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: dynamic shifting range, always at bottom of column

    I'm afraid I can't do that due to the confidential nature of the project. To be frank, the original dynamic range given is not correct because it shifts right rather than down.

    I actually think that the range just needs to be defined as the last two values in the two columns. Is there a way to code this by using the End(xlDown) idea?

    Also, if it's possible, I'd like the range to be the last nonzero values in the column. Because the data is constantly expanding, I have pre formatted the cells below the last cells with values and excel is moving the range to the bottom of those cells rather than the ones that have data in them. I think that the End(xlDown) works for this.

    Would something somewhat like this work?

    Please Login or Register  to view this content.
    Is this making any sense?

    Thanks so much for your help. Any thoughts you can offer me would be much appreciated.

  10. #10
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,653

    Re: dynamic shifting range, always at bottom of column

    Maybe like this:
    Please Login or Register  to view this content.

  11. #11
    Forum Expert Palmetto's Avatar
    Join Date
    04-04-2007
    Location
    South Eastern, USA
    MS-Off Ver
    XP, 2007, 2010
    Posts
    3,978

    Re: dynamic shifting range, always at bottom of column

    To be frank, the original dynamic range given is not correct because it shifts right rather than down.
    Impossible as given and not demonstrated when I tested it. Did you alter it or misuse it in some way?

    I'm afraid I can't do that due to the confidential nature of the project.
    You can remove or dummy up the real data as well as remove the meta-data from the file header.

  12. #12
    Registered User
    Join Date
    08-10-2009
    Location
    Fort Collins, Colorado
    MS-Off Ver
    Excel 2003
    Posts
    29

    Re: dynamic shifting range, always at bottom of column

    It's just the strangest thing, the code looks like it should work but when I type
    Please Login or Register  to view this content.
    in the immediate window, it gives me that same error "Object-defined or application-defined error". I think that your code works palmetto but I am just stuck trying to figure out why this error keeps appearing. The range should exist and I didn't modify what you provided me with.

    Again any help you can give me I'm grateful for. Have either of you seen that error before?

    Thanks much

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1