Hi Kiran123,
Below is an option to go to the next non empty cell.
Be noted that basically this action simply finds the next non-empty cell similar to when done manually by pressing Ctrl Down arrow on any cell. It does not actually go on finding on the entire sheet whether a cell really contains any text or not.
- If you want to add a new action in the Excel VBO itself then go to any tab and make a duplicate copy of it and give it a new name. I made a copy of the Go to Cell action.
- The new action looks this way
View attachment 1544336304954.png
- Start stage Input parameters:
View attachment 1544336354914.png
- End stage Output parameters:
View attachment 1544336401887.png
- Code stage Input parameters:
View attachment 1544336707287.png
- Code stage Output parameters:
View attachment 1544336733449.png
- Code stage
Code:
Dim excel as Object = GetInstance(handle)
RowNumber = excel.Range(cellref).End(-4121).Row '-4121:=xlDown
CellAddress = excel.Range(cellref).End(-4121).Address(False,False)
- Excel sheet and output
after running the new action:
FromCell is the Input given to the new action and RowNumber and CellAddress are the outputs of the action.
View attachment 1544337104075.png
Likewise provide the Output parameters.
To read the value you can use the 'Get cell value' action on the CellAddress data item.
View attachment 1544335512312.png
After executing the new action what you do with the output data items is your logic.
A circular path can be used so that this action executes in a loop. Each time using a Calc stage you need to update the FromCell data item with the new CellAddress data item so that a loop like circular path is formed. This needs to continue based on the logic you use - till you have reached the last row that contains data (can be found used Get Number of rows action) OR till the diagram has reached the last row of Excel (meaning there are no further rows in excel itself) depending upon what logical condition you have put. Be noted that if the Row Number reaches 1048576 (for latest Excel versions) then there are no further rows in Excel. You need to add a stopping condition to the above loop else it will go on executing which would either be a hard coded row number OR when it reaches [Get Number of rows] OR the last row of Excel.
