Dynamic Range

balling_threes

Board Regular
Joined
Jun 30, 2009
Messages
85
Hey,

I'm trying to figure out how to name a range based on values within the sheet.
For example:
I have a table that looks like this:
<table style="border: 1pt solid rgb(163, 163, 163); border-collapse: collapse; direction: ltr;" valign="top" border="1" cellpadding="0" cellspacing="0"><tbody><tr><tr><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 1.127in; vertical-align: top;">
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 1.12in; vertical-align: top;">1
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.667in; vertical-align: top;">2
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.672in; vertical-align: top;">3
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.715in; vertical-align: top;">4
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.714in; vertical-align: top;">5
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.75in; vertical-align: top;">6
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.779in; vertical-align: top;">7
</td></tr><tr><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 1.127in; vertical-align: top;">A
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 1.12in; vertical-align: top;" align="center">x
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.667in; vertical-align: top;" align="center">5
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.672in; vertical-align: top;" align="center">3
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.715in; vertical-align: top;" align="center">34
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.714in; vertical-align: top;" align="center">23
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.75in; vertical-align: top;" align="center">34
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.779in; vertical-align: top;" align="center">x
</td></tr><tr><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 1.127in; vertical-align: top;">B
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 1.12in; vertical-align: top;" align="center">x</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.667in; vertical-align: top;" align="center">500
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.672in; vertical-align: top;" align="center">222
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.715in; vertical-align: top;" align="center">345
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.714in; vertical-align: top;" align="center">1234
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.75in; vertical-align: top;" align="center">1234
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.779in; vertical-align: top;" align="center">x
</td></tr><tr><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 1.127in; vertical-align: top;">C
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 1.12in; vertical-align: top;" align="center">x</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.667in; vertical-align: top;" align="center">$96.44
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.672in; vertical-align: top;" align="center">$99.51
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.715in; vertical-align: top;" align="center">$102.57
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.714in; vertical-align: top;" align="center">$111.76 </td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.75in; vertical-align: top;" align="center">$120.95
</td><td style="border: 1pt solid rgb(163, 163, 163); padding: 4pt; width: 0.779in; vertical-align: top;" align="center">x
</td></tr></tr></tbody></table>
I want to create range from A2:C6. The range will always be 3 rows high. But the number of columns in the range will be different everytime.

So I want the range to start one cell to the right the first X (column 2 in this case), and end one cell to the left of the next column with an "X" in it (column 6 in this case). And I want to name the range "start".

I've been looking at beginning name ranging, but could not figure it out. Does any one have any ideas or suggestions on how I could approach this?

Thanks so much, Guillaume.
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Will there always be x values after the last column of numbers? Like this:
Excel Workbook
ABCD
2111x
3111x
4111x
5111x
6111x
Sheet3
Excel 2010

or will there be not x values like this:
Excel Workbook
ABCD
2111
3111
4111
5111
6111
Sheet3
Excel 2010
 
Upvote 0
This Defined Name formula should work with or without x values:

='Dynamic Range'!$A$2:INDEX('Dynamic Range'!$4:$4,MATCH(10^100,'Dynamic Range'!$4:$4))

Here is how I created and entered this Defined Name:

1. The data was placed on a sheet named "Dynamic Range". If you sheet has a different name replace the words “Dynamic Range” with the name of your sheet.
2. Use Ctrl + F3 to open Defined Name dialog box (Name Manager in Excel 2007 or later).
3. Type name ‘Start’ in Name textbox (in Excel 2007 hit New button first)
4. Enter formula in “Refers to” textbox.
5. Click OK.

The number 10^100 is some number that will always be bigger than the largest value in row 4.

This assumes that the data will always start in cell A2, there will always be three rows.

This would work on data sets like these:
Excel Workbook
ABCDE
1
2533423x
35002223451234x
496.4499.51102.57111.76x
5
Dynamic Range
Excel 2010

or
Excel Workbook
ABCDEFG
1
253342311x
3500222345123411x
496.4499.51102.57111.7611x
5
Dynamic Range
Excel 2010

or
Excel Workbook
ABCD
1
25334
3500222345
496.4499.51102.57
5
Dynamic Range
Excel 2010

See if that works.
 
Upvote 0
If those are the only two X's in that row (first one optional), you could try

Name: Start
RefersTo: INDEX($2:$2, 1, CHOOSE(COUNTIF($2:$2,"x"), 1, MATCH("x",$2:$2,0))):INDEX($5:$5, 1, MATCH("x",$2:$2))
 
Upvote 0
Thanks for the help. I'll try that out. The solution you give though is for a specific cell formula; do you know if there is a way to mimic that through VBA code.
 
Upvote 0
Code:
Dim myRange As Range
Dim leftCell As Range
Dim rightCell As Range

With Rows(2)
    Set leftCell = .Find(What:="x", after:=.Cells(1, .Columns.Count), LookIn:=xlValues, LookAt:=xlWhole, _
        SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)

    Set rightCell = .FindNext(after:=leftCell)
    If leftCell.Address = rightCell.Address Then Set leftCell = .Cells(1, 1)
End With
Set myRange = Range(leftCell, rightCell)
 
Upvote 0
It looks like mikerickson has you covered for the VBA.

However, if you did use the formula that I proposed in post #4, that will get the correct range only after all the number data is entered in the last column. If you are doing a data dump, then the formula works fine. If you are not doing a data dump (say you are typing data), if you want the dynamic range to work as soon as any number data is entered in the last number column, try this:

=Sheet2!$A$2:INDEX(Sheet2!$4:$4,SUMPRODUCT(MAX((Sheet2!$2:$4<>"")*COLUMN(Sheet2!$2:$4)*ISNUMBER(Sheet2!$2:$4))))

In Excel 2010 you might ry:

=Sheet2!$A$2:INDEX(Sheet2!$4:$4,AGGREGATE(14,6,(Sheet2!$2:$4<>"")*COLUMN(Sheet2!$2:$4)*ISNUMBER(Sheet2!$2:$4),1))
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top