Region Function

Let’s assume, a situation occurs where you have to perform a dynamic selection of input data with a frequent change in the number of rows and columns. This can be achieved by using a combination of functions primarily with region functions.

Region functions are dependent and not stand-alone functions i.e., they are used with other functions in order to achieve a specific action, since SheetKraft currently doesn’t allow the auto expansion for this function. So, it is recommended to wrap the region function in selectrows.sk or writearray.sk function.

RegionDown.SK: - This function returns the cell reference of non-blank cells vertically downwards from a selected cell/ row range.

RegionRight.Sk: - This function returns the cell reference of non-blank cells horizontally towards right from a selected cell/ column range.

RegionAcross.Sk: - This function selects the largest region of non-blank cells across vertically downwards and horizontally towards right the selection goes on until the occurrence of non-blank row/column.

Region functions can be used widely across various array based functions like vlookup, import and export functions, pivot, stack rows, filter, fill, etc.

Let us consider some cases:

1) You have to select a region from an input range with change in number of rows.

2) You have to select a region from an input range with change in number of columns.

3) You have to select a region from an input range with change in both rows and columns.

For the first part, you have to use regiondown.sk, since the desired input range has a definite column range:

For the second part, you have to use regionright.sk, since the desired input range has a definite row range:

For the third part, you have to use regionacross.sk, since the desired input range has an indefinite row and column range:

A strict note for region functions, there shouldn’t be a region of blank cells as input in region functions