Search code examples
excelvba

Excel VBA, Range return of User-defined function not working with OFFSET in formula

I'm working with ranges of data with variable length. Therefore to get the entire range, I made a short excel function:

Function UntilBlank(StartCell As Range)
Dim lastCell As Range
Dim currentCell As Range

Set lastCell = StartCell.Cells(1, 1)
Set currentCell = StartCell.Offset(1, 0)

While Not (IsEmpty(currentCell.Cells(1, 1)))
    Set lastCell = lastCell.Offset(1, 0)
    Set currentCell = lastCell.Offset(1, 0)
Wend

UntilBlank = Range(StartCell.Cells(1, 1), lastCell)

End Function

This works great for most cases. However, when I try to use this function within an OFFSET as its first parameter, OFFSET just returns a single #VALUE cell.

My expectation is the return of my function works like any other range. The .address of the return looks perfect, I just don't understand why offset is having trouble taking that range reference and offsetting it normally.

Solution

  • Three letters more will resolve your problem

    Set UntilBlank = Range(StartCell.Cells(1, 1), lastCell)

    Now the function returns the Range instead of an array of values.