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.

### Like this:

Like Loading...

*Related*

Reblogged this on Sutoprise Avenue, A SutoCom Source.

LikeLike

Pingback: Daily Download 33: Miscellaneous | Newton Excel Bach, not (just) an Excel Blog

Pingback: 6 Year Report | Newton Excel Bach, not (just) an Excel Blog