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