Newton Excel Bach, not (just) an Excel Blog

In the process of tidying up the ConBeamU spreadsheet (which will be posted here in the next few days) I decided to revise the routine for converting input ranges to arrays.  The input for the continuous beam analysis functions consists of several ranges of variable length.  The functionality required of the routine to read the data is:

• Extend or reduce the range size to the length of continuous data in the first column.
• Return the number of rows and columns in this range.
• Convert the range object to a variant array

This task is performed by the EndDown function below:

Function EndDown(InRange As Variant, Optional Vol As Boolean = False, Optional NumRows As Long = 0, Optional NumCols = 0) As Variant
Dim SelectRows As Long, NextRow As Variant, LastRow As Long, TopRow As Long

    If Vol = True Then Application.Volatile

    If TypeName(InRange) = "Range" Then
        SelectRows = InRange.Rows.Count
        NumCols = InRange.Columns.Count
        TopRow = InRange.Row

        '  Check for a single row
        NextRow = InRange.Offset(1, 0)(1, 1).Value2
        If IsEmpty(NextRow) = True Then
            NumRows = 1
            InRange = InRange.Resize(1).Value2
        ' Else use xlDown to return all rows to the first blank cell
        Else
            LastRow = InRange.End(xlDown).Row
            NumRows = LastRow - TopRow + 1
            InRange = InRange.Resize(NumRows).Value2
        End If

    Else
        NumRows = UBound(InRange)
        NumCols = UBound(InRange, 2)
    End If
    EndDown = InRange
End Function

This function, and two related functions, EndRight and EndBoth, can be downloaded from GetRange.xlsb, including full open source code.

These functions can also be used on the spreadsheet, as shown in the screenshot below:

EndDown, EndRight and EndBoth functions combined with Sum function

Note that by default any changes to data outside the selected range (outside the yellow range in the screenshot) will not cause the functions to recalculate. To change this behaviour, so that a change to a cell value anywhere on the spreadsheet will trigger a recalculation, set the optional second function argument to TRUE, as shown in cells H11 and I11.