Combining the Offset function with the Intersect operator

[JenniferMurphy]

I need to pass a range to a UDF. I would like to use named ranges. I may need to expand the range by adding rows or columns including at the ends of the range. But if I am not careful, rows or columns that are added at the ends of the range may or may not be included in the expanded range. My solution is to name the rows and columns that are just outside the range. But this presents a problem for the UDF, because the range is too large.

I have come up with two possible solutions. I would appreciate any comments or suggestions.

Here's the table. The columns have been assigned the names shown in Row 4. That is, Col D is named "ProdA". Similarly, the rows have been assigned the names shown in Col C.

[TABLE="class: grid, width: 400"]
<tbody>[TR]
[TD]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: center"]Prod0[/TD]
[TD="align: center"]ProdA[/TD]
[TD="align: center"]ProdB[/TD]
[TD="align: center"]ProdC[/TD]
[TD="align: center"]ProdD[/TD]
[TD="align: center"]ProdN[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Row_1[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Row_2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Row_3[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]12[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Here's the UDF code. All it does is return the list of addresses of the cells in the range it was passed.

Function RangeTest(pRange As Range, Optional pPlus2SW As Boolean = False) As String

Dim RowBeg, RowEnd, ColBeg, ColEnd, i, j
RowBeg = 1: RowEnd = pRange.Rows.Count
ColBeg = 1: ColEnd = pRange.Columns.Count
If pPlus2SW Then
  ColBeg = 2: ColEnd = ColEnd - 1
End If

RangeTest = ""
For i = RowBeg To RowEnd
  For j = ColBeg To ColEnd
    RangeTest = RangeTest & " " & pRange.Cells(i, j).Address
  Next j
Next i

End Function

And here are the results.

[TABLE="class: grid, width: 820"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]K[/TD]
[TD="align: center"]L[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]Result[/TD]
[TD="align: center"]Formula[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD]$D$5 $E$5 $F$5 $G$5
$D$6 $E$6 $F$6 $G$6[/TD]
[TD]K5: =rangetest(D5:G6)[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD]$D$5 $E$5 $F$5 $G$5
$D$6 $E$6 $F$6 $G$6[/TD]
[TD]K6: =rangetest((D:D 5:5) : (G:G 6:6))[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD]$D$5 $E$5 $F$5 $G$5
$D$6 $E$6 $F$6 $G$6[/TD]
[TD]K7: =rangetest((ProdA Row_1) : (ProdD Row_2))[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD]$C$5 $D$5 $E$5 $F$5 $G$5 $H$5
$C$6 $D$6 $E$6 $F$6 $G$6 $H$6[/TD]
[TD]K8: =rangetest((Prod0 Row_1) : (ProdN Row_2))[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD]$D$5 $E$5 $F$5 $G$5
$D$6 $E$6 $F$6 $G$6[/TD]
[TD]K9: =rangetest((OFFSET(Prod0,0,1) Row_1) : (OFFSET(ProdN,0,-1) Row_2))[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD]$D$5 $E$5 $F$5 $G$5
$D$6 $E$6 $F$6 $G$6[/TD]
[TD]K10: =rangetest((Prod0 Row_1) : (ProdN Row_2),TRUE)[/TD]
[/TR]
</tbody>[/TABLE]

In Row 5, I pass the UDF the literal range I want processed. This is the correct result.

In Row 6, I do the same, but using the Intersect operator.

In Row 7, I do it using the named rows and columns. This works, but has the problem mentioned above if I were to expand the range.

In Row 8, I use the names of the columns just outside the range. This includes those extra cells, which is not what I want.

In Row 9, I use the Offset function, which I didn't think would work, but it seems like it does. Are there any problems with this approach?

In Row 10, I use the optional pPlus2SW parameter to tell the UDF to skip the first and last cells in each row. This also works and seems simpler to me.


Does anyone have any comments?

 

[James006]

Hi Jennifer,

Are you defining your named ranges in a dynamic way ...???

 

[JenniferMurphy]

Hi Jennifer,

Are you defining your named ranges in a dynamic way ...???

I had not heard about dynamic ranges before. Thanks for mentioning them. I did a little reading. It looks like I'll need some time to study them.

I may be back later...

 

[James006]

Hi Jennifer,

Regarding Excel Dynamic Named Ranges ...

The best possible explanation ... is Debra's ... :wink:

https://www.contextures.com/xlNames01.html#videodynamic

Hope this will help