Selecting Ranges from a UDF

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:

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…

View original post 46 more words