How to delete particular row in excel

Help Activities
,
1

Hi Team,
I have excel with multiple data , Please find the attachment for your reference

IMG_20220909_135202_1
IMG_20220909_135202_11908×4032 1.47 MB

In that attachment, BYD=“55602960” and “55719321” . Need to remove this entire rows
Please suggest.

How to delete above entire rows from the excel and I want remaining data in the same excel with headers.

Please help me to slove this issue

2

Hello @Baby123
You can use Insert/Delete rows activity in excel scope

image
image795×326 7.81 KB

Define the position that you need to delete and change the mode to remove for delete rows

1 Like
3

Hi @Gokul_Jayakumar ,
Thanks for quick response.

I tired this one but I have problem with row position.
I don’t know which position those above values are there .
So I want pass position dynamically.

Kindly suggest

4

@Baby123
Use Look up range and pass the value you need to find
example lookupu value is 55725781 and output variable stored in String “CellPosition”, it will print as a “B4”
Useing regex you cant split number

Sysytem.Text.RegularExpression.Regex.Match(cellPosition,"\d+").tostring.trim

image
image806×150 4.37 KB

5

@Baby123
please follow these steps.
1- use read range to read data from excel in datatable.
2- use for each loop over datatable.
3- put if condition and place condition where you want to match data.
4- in then block put delete/insert row activity and put (For Each ->Row)
5- outside loop write range activity and give datatable and output file.

6

@Baby123 This is the easiest way. without linq or another complex method

7

Hi @raja.arslankhan ,

In that insert/ delete activity how to pass position as dynamic.

I don’t know in which rows those values are there
At that time how can I mention position in insert/delete activity to delete entire row.

And also I want to delete previous mentioned 2 Types of duplicate values only .
I want remaining data and headers.

Thank you

8

Hi @Gokul_Jayakumar ,

We will try and update you

9

I have another issue like
I wnat to create column at end of the excel for example I created one column like Status and reasonofFailure

So I want to write in that columns as fail and mention the reason also.

Here my issue is how I know in which column position to write the fail and reason
.
Please find the attachment for your reference.
For example I want create column in M column and then enter the values as fail and reason one by one for that I using write cell activity,
In that activity how to pass M cellvalue as dynamic.

Please suggest
Processing: IMG-20220909-WA0012.jpeg…

11

Hello @Baby123 , Screen shot image is not visible

Kindly try this

  1. Read the excel file in excel application scope and save as Dt variable
  2. Insert / Delete Columns and check the property Change mode as Add
    Position Dt.columns.count+1
    image
    image809×349 10 KB
12

Use Looku Up range
image
You can get position of word status For example “M1”

System.Text.RegularExpressions.Regex.Match(YourString,"\D+").ToString.Trim

You can Get M, In write cell use M+NumberString

13

HI @Baby123

Try like this

  • Use Excel Application and read range activity and store the value in the datatable

  • Use Filter Datatable activity and configure it.

    image
    image730×751 25 KB

  • Use write range and give the new sheet name and check on add headers in properties and write the data table in the new sheet.

  • Delete the old sheet using modern activity(Find the activity after enabling show modern in the filters)
    image

Or you can use that sheet as the initial inout with no changes

Hope this Helps!!

Regards
Sudharsan

14

@Baby123 give me time I can try with code. I implemented this but I can try again

15

@Baby123 Can you send me sample data.

16

@Baby123

Best would be to filtwr on byd using filter excel activity

And then use delete rows with visible option…it would delete all the rows

Cheers

1 Like