Filter and remove the datarow

Hello guys i have an excel

  1. First i want to check the Net column If the net column is zero then it should check for the gross and return premium if that also is zero i want to remove the column and rewrite on the same excel

Hi @Gokul_Murali

Get the excel data to a datatable say dt_Data, here is the LinQ query

dt_Data = dt_data.AsEnumerable().Where(Function(row) Not (CDbl(row("NET")) = 0 AndAlso CDbl(row("GROSS PREMIUM")) = 0 AndAlso CDbl(row("RETURN PREMIUM"))= 0 )).CopyToDataTable

And write the data table to the result sheet(Make sure to clear the sheet)

Could you please elaborate more on the requirement? Please clarify following questions

  1. I believe your scenario is based on each row rite. In that case - I am not clear on the requirement to delete whole column based on one row condition. Please explain in details

If your requirement is to remove all rows that have zero value in below three columns

Net
Gross Premium
Return Premium

  • Get the details to a data table using read range activity of excel
  • use filter data table and include condition to remove row where
    Net =0 AND GROSS Premium =0 and RETURN PREMIUM =0

Let me know if my understanding is correct. Or if you need more details

@sharazkm32
I have tried this but its not working

@Gokul_Murali

As you need to write on excel…if you read and perform the deletion in datatable and then write it might retain the old data as well…two ways here

  1. Use for each row in excel and inside that use if activity and use your all 3 columns condition…on then side use delete row activity
  2. Read the data into datatable then use clear sheet activity to remove all data…then use linq or filter to remove rows in datatable and then write to excel

In first option delete rows might not work inside for loop…if so save the row number and then loop through those numbers and use delete range inside…make sure to order the numbers from highest to lowest

Hope this helps

Cheers

@Anil_G

I have used remove datarow inside excelscope in if condition, and writing into a new sheet but data is not filtering all the data is writing into the sheet

Result

@Gokul_Murali

If you read the options correctly…using clear sheet is mandatory to use second option

Not sure why you are using excel app scope inside again if you are removing data from datatable

Cheers

Did you try this?
What is wrong here?

1 Like

Thankyou guys :slight_smile:

@Sanjay_Bhat
@sharazkm32
@Anil_G

1 Like

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