# 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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### 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
212
Replies
5
Views
87
Replies
6
Views
678
Replies
6
Views
202
Replies
2
Views
677

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,152,193
Messages
5,768,774
Members
425,492
Latest member
blueexcel123

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