# Formula for dynamic named range of non-contiguous columns

#### CodeNinja

##### Well-known Member
I am trying to create a dynamic named range. There are multiple non-contiguous columns (lets say column B and column F) that have different number of used cells in each column. Lets say I am using B1:B10 and F1:F5, I need to create a named range for that non-contiguous area. Typically with 1 column, I can use offset to get B .... =Offset(B1,,,B:B). This way, if someone adds data in B11, my named range will include B1:B11. I a formula that can have multiple columns be dynamic in this way. I cannot use =Offset(B1,,,B:B):Offset(F1,,,F:F) because it will give me the range B1:F(max used row in B or F). I tried ; and concatenating them but nothing seems to work... any ideas? I do need this to work for more than 2 columns (actually about 15 is ideal).

Thanks,
CN.

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Marcelo Branco

##### MrExcel MVP
I a formula that can have multiple columns be dynamic in this way.
Questions
What do the ranges contain, numbers or texts?
What do you intend to do with non-contiguous ranges combined? Add numbers or what?

M.

#### CodeNinja

##### Well-known Member
Hi M. Thanks for the reply.

Ranges are unconstrained (can contain numbers or text).
In vba I will need to loop through the cells in the named range.

#### Marcelo Branco

##### MrExcel MVP
Maybe something like this (not fully tested)
Assuming data in Sheet1

Formulas > Names Manager
New
Name: Range1
Refers to: =Sheet1!\$B\$1:INDEX(Sheet1!\$B:\$B,MAX(MATCH(REPT("z",255),Sheet1!\$B:\$B),MATCH(9.99E+307,Sheet1!\$B:\$B)))
Ok

New
Name: Range2
Refers to: =Sheet1!\$F\$1:INDEX(Sheet1!\$F:\$F,MAX(MATCH(REPT("z",255),Sheet1!\$F:\$F),MATCH(9.99E+307,Sheet1!\$F:\$F)))
Ok

New
Name: FullRange
Refers to: =Range1,Range2
Ok

Sheet1
Pasta2
BCDEF
1zzzttt
2vvv10
3zzzkkk
4vvvzzz
5zzz45
68
7zzz
8vvv
97
10aaa
1134
Sheet1

Some tests in VBA
VBA Code:
``````Sub aTest()
Dim r As Range, lCounter As Long
Dim rCell As Range

Set r = Range("FullRange")
For Each rCell In r
If rCell.Value = "zzz" Then lCounter = lCounter + 1
Next rCell
MsgBox lCounter
MsgBox Application.Sum(Range("FullRange"))
End Sub``````

Hope this helps

M.

#### CodeNinja

##### Well-known Member
Hi Marcelo,
I think this works for me more or less... I am still using the offset formula, but am using them as helpers to generate the main loop through formula using =offsetFormula1,offsetFormula2...

Thanks for the help.

CN.

Replies
0
Views
176
Replies
6
Views
615
Replies
6
Views
123
Replies
2
Views
352
Replies
2
Views
491

1,148,013
Messages
5,744,343
Members
423,863
Latest member
teehexcel

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

### Which adblocker are you using?

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

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