How to copy down data from previous rows in empty rows

[Karthik23 (Karthik)]

Hello everyone,

I’m working on a data table manipulation, where I’m trying to copy paste the data from previous row in the empty rows until a values comes up.

In row1 I have value ISA001 and next 5 rows are empty, the empty rows needed to be filled with the row1 data. And in row 7 I have value ISA002 and next few rows are empty and needed to be filled with row11 data and so on., I’m attaching a screenshot for reference.

Note: the number of empty rows are not constant.

Can anyone please help me with a logic to accomplish this task.

Thanks in advance.

[raja.arslankhan]

@Karthik23

Hello everyone,

I’m working on a data table manipulation, where I’m trying to copy paste the data from previous row in the empty rows until a values comes up.

In row1 I have value ISA001 and next 5 rows are empty, the empty rows needed to be filled with the row1 data. And in row 7 I have value ISA002 and next few rows are empty and needed to be filled with row11 data and so on., I’m attaching a screenshot for reference.

@Karthik23

1. Read the data table into a variable using the Read Range activity.
2. Use a For Each Row activity to iterate through each row in the data table.
3. Inside the loop, check if the value in the first column (e.g., ISA column) is not empty. If it is not empty, store the value in a variable.
4. If the value is empty, use a Write Cell activity to paste the stored value in the corresponding cell.
5. Continue iterating through the rows until a new value is encountered, at which point you update the stored value.
6. Finally, write the modified data table back to the Excel file using the Write Range activity.

[LAKSHMI_NARAYANA_PEMMASAN (LAKSHMI NARAYANA)]

try as below

Using excel scope , use excel file
for each row activity to iterate through rows
use if condition to check if it is empty

String.IsNullOrEmpty(CurrentRow.ByField("Segment")) = true OR String.IsNullOrWhiteSpace(CurrentRow.ByField("Segment")) = true

then block use fill range or write cell activity for now i have used fill range

ExcelSource.Sheet("Sheet1").Range(CurrentRow.ByField("Segment").Address)

and provide value of text you read from else block

else block use read cell activity and store the value in a varibale

CurrentRow.ByField("Segment")

Hope this helps

[Karthik23 (Karthik)]

@LAKSHMI_NARAYANA_PEMMASAN , @raja.arslankhan . Thank you for your replies. I have figured out the logic my self.

I’m actually manipulating the data table and not excel. The solution for my issues is as follows:

I’m looping through the datatable and inside for each row for data table I’m took IF block.

The condition for IF is currentrow.item(“Segment”).tostring.trim = “”
Then currentrow.(“Segment”) = dt.(idx-1)(“Segment”).tostring

where dt is the datatable that i’m looping and i’m taking the index of the for each row in datatable and getting the value of previous cell by subtracting the index by 1.