Remove rows after A10 cell

[Cuberoot]

I have one excel file in which there are multiple duns number.I want to filter the duns =688955830
and remove the remaining duns number in the same excel sheet.how to do it?
Output should be like this-

data is like this-

TIA

[Shiva_Nikhil]

@Cuberoot

Try with remove duplicates or

If you want to remove in excel

Use
Delete range activity

[Cuberoot]

I donot know about the range and in Remove duplicate row activity-it will remove duplicate rows or different rows?

[Shiva_Nikhil]

@Cuberoot

remove duplicate rows Activity will remove the duplicate rows

[Cuberoot]

So what should I do to remove all the rows after 10 row?

[Shiva_Nikhil]

@Cuberoot

If you know the index then use delete range activity and mention the starting range

or you want to remove duplicates only?

[Cuberoot]

I want to delete the all data after A10.After this activity which activity should I used?
TIA

[Shiva_Nikhil]

@Cuberoot

no need to use any other activity it will remove all the rows after A10

[Cuberoot]

It shows error range doesnot exist

[Shiva_Nikhil]

@Cuberoot

mention the range Starting cell and last cell range

as Like “A10:H20”

[Cuberoot]

but i don’t know the ending range.So how to do it?

[Shiva_Nikhil]

@Cuberoot

if you are not using read range for reading the excel then use read range activity and use below expression in assign

outputdt=datatable.Asenumerable.take(9).copytodatatable then no need to delete in excel

[Cuberoot]

it is not deleting the data

[Shiva_Nikhil]

@Cuberoot

Use write range activity to write the data to excel

After assign use write range activity and pass the outputdt in the datatable

[supermanPunch (Arpan)]

Hi @Cuberoot ,

Could you try the below approach :

1. Use Read Range Activity and keep the Range property as “A9” and Uncheck Add Headers Option. Get the datatable value in variable, lets say DT.

2. Next, You can use Filter Datatable activity to Filter values to have only rows containing the DUNS number value, For that follow the below Configuration in Filter Datatable activity.
   

3. Next, there are multiple ways to proceed from here :
   a. As you would require to have it in the same Excel sheet, you could create a Template of it already kept ready, Copy the Template Excel sheet and Write the Filter Datatable Output to it using Write Range activity starting from Range A9.
   b. You could use Write Cell activity to clear the Existing data present in the Excel sheet Starting from Range “A9” and after clearing use Write Range activity to Write the Filtered Datatable output. A more detailed on clearing the data range is provided in the below Post :

Delete Excel data range without Excel Installed

@kasey.betts , Assuming that we have the Data already available in Datatable, which Represents the Data in the Excel, we can use it’s Column Count and Row Count information to Clear the Contents using Write Cell Activity. For Example : Let the Datatable be DT We get the Column Letter of the Excel by using the Column Count information as follows : columnLetter = UiPath.Excel.Helpers.ExcelUtilities.ConvertColumnIndexToColumnLetter(DT.Columns.Count+1) The above would help us get the Col…

Here, Also assuming that the Filtered Rows is needed to be Preserved and not the way of Removing the rows after a certain row number.

[Cuberoot]

it works but it didnot delete the data from rows how to create template.output is like this-

.Can we remove the red color from the empty cells?
TIA

[supermanPunch (Arpan)]

@Cuberoot ,

Could you provide us with the Details of the Implementation done so far ? We could maybe try analyse and tweak the necessary parts to get the required output.

[Cuberoot]

how to get the borders in empty cell?

TIA

[supermanPunch (Arpan)]

@Cuberoot ,

I did check with Workbook Write Cell activity, when writing the cell with empty value, it did not remove the Borders.

As mentioned previously, we would require to understand your implementation done so far, to provide further corrections/suggestions.