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.
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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
 

balling_threes

Board Regular
Joined
Jun 30, 2009
Messages
85
Hey Mike,

There always be x values after the last column of numbers, as in your first example.

Thanks, Guillaume.
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920

ADVERTISEMENT

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

balling_threes

Board Regular
Joined
Jun 30, 2009
Messages
85
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.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920

ADVERTISEMENT

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)
 

mgirvin

Well-known Member
Joined
Dec 15, 2005
Messages
1,213
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))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,399
Messages
5,601,452
Members
414,450
Latest member
Cassy_sn

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
Top