Formula for dynamic named range of non-contiguous columns

CodeNinja

Well-known Member
Joined
Feb 18, 2013
Messages
643
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
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Feb 18, 2013
Messages
643
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
Joined
Aug 23, 2010
Messages
16,907
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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
Joined
Feb 18, 2013
Messages
643
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.
 

Forum statistics

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