This document is here to show and explain the behavior of some dynamic range Excel activities.

For all the activities we will use this excel and show the result of the activity with it.

Get Row From Data

Parameters:

• referenceCell (if not filled, the active cell is used)
• verticalDirection (up or down, the default direction is down)

Output:

• the index of the row

Behavior:

The activity return the index of the last row with data. That means that if in the column (either the column of the active cell or the column of the referenceCell) there is a cell without any data it will return the index of the row before this cell.

Example:

If we launch the activity with the A1 cell as the reference cell the output will be 19. But if we put D1 as the reference cell, the output will be 3. Why 3? Because the D4 cell is empty so the count stop there. This is an excel behavior that can be simulated by pressing CTRL + DOWN ARROW (or UP ARROW) directly in Excel.

Get Column From Data

Parameters:

• referenceCell (if not filled, the active cell is used)
• horizontalDirection (left or right, the default direction is right)

Output:

• the index of the column. The index is a number that correspond to the letter of the column in Excel, A->1, B->2, … AB ->28, etc.

Behavior:

The activity return the numerical index of the last column with data. That means that if in the row (either the row of the active cell or the row of the reference cell) there is a cell without any data it will return the index of the column before this cell.

Example:

If we launch the activity with the A1 cell as the reference cell the output will be 8 (the index of the H column is 8). But if we put A4 as the reference cell, the output will be 2. Because it the same as the row, the index is the column of the last non empty cell on a row. So here the C4 cell is empty so the column is B which index is 2. This is an excel behavior that can be simulated by pressing CTRL + RIGHT ARROW (or LEFT ARROW) directly in Excel.

Note : For both activities, if you reference a blank cell, the output will be the index of the first non blank cell of the row or the column referenced.

Get Used Range Row

Output:

• the index of the last used range row 

Behavior:

The activity return the index of the last row used. It means that even if you delete the data of the last row used, the activity will still return the index of this one.

Example:

If we test the activity with the excel as it is (without putting data further than the H19 cell) we will have 19 as the result. Now if we put some data further so for example in the J21 cell, we will have 21 as the result which is expected.

Now we can test if we delete the data of the J21 cell, we will still have 21 as the result. We can test this behavior by pressing CTRL + END on excel, it shows the last cell used.

We can see here, if we delete the data from the J21 cell than do a CTRL + END, Excel will focus the last cell which is still J21.

Get Used Range Column

Output:

• the index of the last used range column

Behavior:

The activity return the index of the last column used. It means that even if you delete the data of the last column used, the activity will still return the index of this one.

Example:

If we test the activity with the excel as it is (without putting data further than the H19 cell) we will have 8 as the result (H has for index 8). Same as for the row, when we put “hello” on the J21 cell, the activity will output 10, and if we delete the data it will still output 10.

As you may have noticed the column indexes can be letters as well as numbers. The activities we have seen above, on the columns, outputs numbers. So it can be useful to have activities that can convert indexes to letters and the other way around too.

Convert Column Index to Name

Parameters:

• Column Index

Output:

• Column Name

Behavior:

This activity converts the column index to a column name. For the index 2 it will output B, for 42 it will output AP, etc.

Convert Column Name to Number

Parameters:

• Column Name

Output:

• Column Index

Behavior:

This activity converts a column name to the column index. For the name B it will output 2, for AC it will output 29.

Conclusion

In this blog post we saw the different activities for handling dynamic range in excel and how Excel behave in some cases.