How to remove duplicate rows and empty rows if my values starts from column F11 onwards in between of excel

Can anyone pls help me,
How to remove duplicate rows and empty rows only in column “F” where I am using read column activity as shown in the image and my values start from row F11 onwards.

Hi @tkiran
You can try this method as well

1. Read the excel and store in dt1 (read from desired range)

2. Now use the assign activity

dt2= dt1.AsEnumerable().GroupBy(Function(r) r(columnname).ToString).Select(Function(g) g.First).Where(Function(r1) r1(columnname).ToString.Trim<>"").CopyToDataTable

3. Now write the dt2 to the excel sheet.

Attaching a sample excel file for this
Main.xaml (12.7 KB)

Nived N
Happy Automation

Hi Nived,
Thank you for your Reply,

I can get the correct output if my write range is in another sheet. and in output sheet also i am getting other columns also which i do not want

Actually I need to write in the same sheet but when I write in the same sheet in row “F10” duplicate rows are not removed.
can you pls help me how to write in the same sheet.

Hi @tkiran
before writing the datatable to same sheet, use Delete Range activity to delete the required rage [in ur case, it is excel table] and then write it to it using write range.

Nived N

Hi Nived,
I do not want to delete anything just I want to remove duplicates and empty rows in “sheet 1” in column “F” only

Yes @tkiran

Actually delete range will remove the darat and write range will add the updated data

Try that please

Hi Nived,

I was confused what to give in range field so i jus tried “F9 : F1000”
Finally got it thank u so much

Hi @tkiran

You can give the entire range of excel data from where the datatable data is stored

1 Like

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