Dynamic Range that expands with new columns

[gaudrco]

I am trying to create a dynamic range that expands with new columns added. I created a defined name called ("DynamicCompList") and the range for this is D7:R57. When I enter information into S7, I want the range to expand to D7:S57. My data in the range has some blanks that need to stay. I tried to refer my defined range to this formula but I couldn't get it right:

=OFFSET($D$7,0,0,COUNTA($7:$7),17)

Also, I set my width to 17 because that how many columns I have currently but I have a feeling this will be an issue because as the range increase, the width should also increase since the dynamic range increases by columns.

Any ideas?

 

[RasGhul]

try

=Sheet1!$D$7:INDEX(Sheet1!$7:$1048576,COUNTA(Sheet1!$D:$D),COUNTA(Sheet1!$7:$7)+3)

 

[gaudrco]

Thank you very much!

 

[gaudrco]

try

=Sheet1!$D$7:INDEX(Sheet1!$7:$1048576,COUNTA(Sheet1!$D:$D),COUNTA(Sheet1!$7:$7)+3)

How would you adjust this to have the range columns go all the way to the bottom of the sheet instead of a specified range?

Instead of this dynamic range - (D7:R57)
I'm now looking for this dynamic range - (D:R)

 

[NoSparks]

hello gaudrco

I have no idea what are you trying to do, but
tables are dynamic by default, so why not use the table to get what you want ?
Or is it necessary to involve the 1,048,500 and some-odd rows below your table ?

Can you not adapt my last response to you here to be

With oLo
    Set rng = .Range.Offset(, 3).Resize(, .ListColumns.Count - 3)
End With

 

[gaudrco]

hello gaudrco
why not use the table to get what you want ?

End With[/code]

I cannot use a table because of the way my data is set up. The range I'm using is grouped in sections of 50 rows with an extra row to separate them. I need all the sections to be included in the range. I thought the easiest way to do this would be to use the entire column. I'm using defined names to refer the range so I don't know how I would adopt a VBA code

 

[NoSparks]

Oh, I see.
I wondered why you didn't acknowledge that reply,
you're now dealing with the "Competitor Comparison Data" sheet.
I guess the file you linked to previously is now obsolete and useless for trying to figure out what you're doing
so I'll just bow out now.

Good luck with your project.

 

[gaudrco]

I guess the file you linked to previously is now obsolete and useless for trying to figure out what you're doing
so I'll just bow out now.

How about this idea:

Using VBA to define the range. Could you write a script with this logic?

On sheet "Competitor Comparison Data", starting at cell ("H5"), select all cells right until you reach an empty cell. Then select all cells below that to the bottom of the sheet (or 3000 cells down).

That would work I think

NoSparks, the sample you have should work for this.

 

[NoSparks]

Using VBA to define the range. Could you write a script with this logic?

Can't be done.
A named range needs an address and that would be fixed not dynamic.
A dynamic named range needs a formula and your sheet layout defeats that.

With the workbook I've got, I believe this would be what you're looking for, but see what it selects...
"DynamicCompList" refers to
='Competitor Comparison Data'!$H$5:INDEX('Competitor Comparison Data'!$5:$1048576,2452-4,COUNTA('Competitor Comparison Data'!$5:$5)+5)

2452 being the bottom of the 'chunks' you've already got
-4 being the first 4 rows you don't want
+5 being the 5 blank columns to the left of column H