How to make a cell range variable

Hi All,

I am having trouble finding a way to use a variable to fill in the cell range for the XLS Control Table from Cell Range node. For each run of the workflow, the cell range for that node could be different. I need the cell range to go for the very first cell to the last in the row, but the length of the row will change for each run. I would like the cell range to change automatically to match the length of the data, so it does not need to be entered manually each time the workflow is run.


I am going from XLS Control Table Generator to cell range. Below is headers from the XLS control table generator.

Please let me know if you have any ideas for me to try out.

Thanks so much,

I would appreciate help with this as soon as possible.


Hey @MollyR,

I went ahead and took a look at this problem you are facing. This is how I solved it, there probably could be better ways of solving it.

The extract column header node can be left un-configured, and the column expressions node will need an expression such as the following “column(numberOfColumns()-1)” this outputs the last column and provides for dynamic output if the sheet range changes. I also outputted a new column named ‘Last Column’ of type String.

Extract Table Dimension can be left un-configured and number to string will take Dimensions as the variable to change. Table to Row node will again take Dimensions column to then turn into a variable.

The string manipulation node will then be used to take the inputs of the number of rows and first and last columns. For this I assumed A1 would be the starting point, and inputted the following “join(“A1:”, $Last Column$, $${SDimensions}$$)”. I appended a column called Excel Range.

Finally in Table Row to Variable I chose the column Excel Range to be converted to a variable. Then in XLS Control Table from Cell Range in the Flow Variables tab I chose the Excel Range Flow Variable.

I hope this helps and gives you enough information to maybe refine this or move on from this blocker!


1 Like

Hi @MollyR,

another option you might want to consider is to use unpivoting option in the XLS Control Table Generator node and use then a Rule Engine node to define the different tag values based on the Row and Column number. (Instead of defining the tag value with the XLS Control Table from Cell Range node)

This workflow on the KNIME Hub shows an example: 07 Conti Nodes - Zebra Table and Header – KNIME Hub


PS: The last chapter of this free ebook gives you more details on this approach: From Excel to KNIME | KNIME

1 Like

Hi @rsrudd ,

I am not seeing a column expressions node pop up for me. Is there a certain extension I need to download to get that node?


@rsrudd the Excel Range column reads A1:S273, but I need it to just be the first row so only the last column is changing (A1:S1). Additionally I need a separate variable that would be E2:E273 where the last row would vary.


@rsrudd @Kathrin

I was able to make the necessary adjustments to get the intended result from your comments. Thank you for all your help!



This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.