Dynamic Range in popluated column

[foxguy]

Is there any good way to create a Dynamic Range Name when it is unknown if there is data somewhere below the range?

Example:
myRange RefersTo: =Offset($D$3,1,0,Counta($D:$D)-1,1)

If it is unknown if there is other data somewhere in column D, is there some way to redefine myRange to exclude it.

[efernandes67]

Hi,

What do you mean by this "Is there any good way to create a Dynamic Range Name when it is unknown..."?

Can you attach a sample file to be more clear.

Elio

[foxguy]

In my example there is a heading in D3 and data in D4:D10.
I want myRange to be D4:D10, but there may or may not be data in D15 or D100. I'm looking for a way to dynamically define myRange so it includes D4:D10 and will automatically include D11 if I enter data into it..

[teylyn]

Hello,

This depends on the scenario. If, for example, your dynamic range will never extend below row 100 and the data that may/may not appear below the range will always be in row 101 or higher, then you could use

myRange RefersTo: =Offset($D$3,1,0,Counta($D$1:$D$100)-1,1)

If that scenario can not be played, you could set a marker for the end of the dynamic range.

For example, in an empty column, say column A, enter a formula like

=IF(AND(D1<>"",D2=""),"RangeEnd","")

Copy down as far as the dynamic range will possibly extend.

Then, define your dynamic range with

=Sheet1!$D$3:INDEX(Sheet1!$D:$D,MATCH("RangeEnd",Sheet1!$A:$A,0)-1)

[foxguy]

Hi teylyn;

You're coming up with the same ideas I had. Unfortunately they don't solve my problem.

I have a file where the user wants to be able to move a column of data. She highlights the top and bottom cell of the data and does a cut & paste. I have no control over what column she moves the data into, so I can't think of any way to determine where a marker should go.

Richard helped me in this post http://www.excelforum.com/excel-prog...nge-limit.html to redefine a dynamic range so when it's moved it stays valid, but it doesn't account for there already being data in the new column.

The only solution I can can come up with is to define it with a definite size (RefersTo: =Offset($D$3,1,0,7,1)) and then in Worksheet_Change() look for data in D11 and recreate the name (RefersTo: =Offset($D$3,1,0,8,1)). But I don't like that at all.