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?

Dynamic Named Range for shifting rows

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

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

.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.

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

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.

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.

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