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