In an excel if a cell contains data empty, how to delete entire row. Can anyone help me with this please

Help Studio
, ,
1

In my excel data contains empty cells, then i want remove entire row. Can anyone help me with this.
please find below screenshot for reference.

image
image1263Ă—63 2.04 KB

2

@PriyaReddy

Use for each row in excel and check if it is empty using if condition

And then use delete rows activity

Or reqd the data into datatable and then filter with empty rows and then write the data back

Cheers

3

@PriyaReddy

dt1.AsEnumerable.Where(Function(a) not a.ItemArray.any(Function(b) b.ToString="")).CopyToDataTable

This expression removes all the rows which are having empty cell values

4

Hi @PriyaReddy

First you get the datatable by reading the excel. Afterwards, you can delete the empty lines with the filter activity and later write the datatable to excel.

image
image1380Ă—580 51.9 KB

Hope this helps :slight_smile:

5

No, It’s not working

6

I tried It’s not working

7

Can you share screenshot of it. You need to mention the column index right in the column field.

8

@PriyaReddy

please tell what is tried and what is not working…else we wont be able to help without details

cheers

9

Here, i want to read particular column for eg. “H”. If in H column cell had data as empty then i should remove entire row.

10

I have tried read a Particular column and taken one for each activity and if condition inside that. Has applied a condition if that column name contains " ", then added delete row activity. and after that writing data.

11

@PriyaReddy

try this

image
image1142Ă—698 39.3 KB

cheers

12

image
image1495Ă—669 59.7 KB

Please find my code for reference.

13

Put column no right it starts from 0, if it is H it will be 7 in column section. Also in read range keep range as blank so that it will take the whole sheet as datatable.

14

@PriyaReddy

image
image1511Ă—685 75.2 KB

Try this way by using Equals

15

image
image1439Ă—592 79.2 KB

Can you tell me what mistake i have given here, Trainee TR01 should be deleted in H column but its not doing.

16

Can you share your Excel file & what you want to do

17

I was not able to send file coz, i was new user. This is data in my excel.here, i want to remove empty cell and Trainee TR01 to Trainee TR20.

image
image1024Ă—1015 43.5 KB

18

@PriyaReddy

did you try the above filter and delete rows directly on excel

cheers

19

Yes, Empty cell is removing but no sure why it’s not deleting Trainee data.

20

Actually, i have implemented other logic but i have more than 2000 rows due to that it’s taking too long to complete process. That’s the reason i want to change my logic to increase accuracy.