Ranges
Ranges represent rectangular selections of cells in Excel. They allow for efficient access, manipulation, and analysis of data within a defined area. XBRange functions are useful for performing operations on multiple ranges, and dealing with error values that built-in Excel functions such as =SUM() do not handle well.
Range Functions
XBRange_Clean
Clean a range: normalize its shape, remove missing values and optionally handle error values
=XBRange_Clean(range, [onErrors])
| Parameter | Type | Description |
|---|---|---|
| range | XlBlockRange |
A range |
| onErrors | String |
Optional Error handling ('drop', 'keep', or 'error') |
Returns
A range
XBRange_Count
Get the number of cells in a range
=XBRange_Count(range)
| Parameter | Type | Description |
|---|---|---|
| range | XlBlockRange |
A range |
Returns
An integer
XBRange_RowCount
Get the number of rows in a range
=XBRange_RowCount(range)
| Parameter | Type | Description |
|---|---|---|
| range | XlBlockRange |
A range |
Returns
An integer
XBRange_ColumnCount
Get the number of columns in a range
=XBRange_ColumnCount(range)
| Parameter | Type | Description |
|---|---|---|
| range | XlBlockRange |
A range |
Returns
An integer
XBRange_Shape
Shape a range
=XBRange_Shape(range, [rowCount], [columnCount], [fillWith])
| Parameter | Type | Description |
|---|---|---|
| range | XlBlockRange |
A range |
| rowCount | Nullable1` |
Optional Number of rows in new range |
| columnCount | Nullable1` |
Optional Number of columns in new range |
| fillWith | Object |
Optional Value to use for any added elements |
Returns
A range
XBRange_Gather
Clean and combine ranges
=XBRange_Gather(range)
| Parameter | Type | Description |
|---|---|---|
| range | XlBlockRange[] |
A range |
Returns
A range
XBRange_GetUniqueValues
Reduce a range to only unique values
=XBRange_GetUniqueValues(range)
| Parameter | Type | Description |
|---|---|---|
| range | XlBlockRange |
A range |
Returns
A range
XBRange_Concatenate
Concatenate cells of a range into a single string
=XBRange_Concatenate(range, [delimiter], [prefix], [suffix])
| Parameter | Type | Description |
|---|---|---|
| range | XlBlockRange |
A range |
| delimiter | String |
Optional A delimiter |
| prefix | String |
Optional A prefix to prepend at the beginning of the returned string |
| suffix | String |
Optional A suffix to append to the end of the returned string |
Returns
A string
XBRange_SumIf
SumIf, handling errors
=XBRange_SumIf(range, range2)
| Parameter | Type | Description |
|---|---|---|
| range | XlBlockRange |
A range |
| range2 | XlBlockRange |
A range |
Returns
A range