Formula for dynamic named range of non-contiguous columns

[CodeNinja]

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.

 

[Marcelo Branco]

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]

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]

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
	B	C	D	E	F
1	zzz				ttt
2	vvv				10
3	zzz				kkk
4	vvv				zzz
5	zzz				45
6	8
7	zzz
8	vvv
9	7
10	aaa
11	34
Sheet1

Some tests in VBA

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]

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.