For Each Datarow

[nilesay]

Hi,

I want to delete rows that match the value equal to yesterday’s date in column x and less than 7:30 in column y. What statement should I write in the if condition for this?

Best regards,

[rohith.prabhu]

Hello @nilesay ,

Try using the below expression in If condition :

row(“x”).ToString.Equals(Now.ToString(“dd-MM-yyyy”)) And (row(“y”).ToString < “07:30” )

Here change the dd-MM-yyyy as per your datetime format.

Let me know if you face any difficulty here.

Regards,
Rohith

[hafiz.ahsan (Hafiz Mohammad Ahsan)]

You can use the below code to work with it

row(“x”).ToString.Equals(Now.ToString(“dd.MM.yyyy”)) And (row(“y”).ToString < “07:30” )

as per your given date time format

Contact if face any difficulty or need any guidance

[Yoichi]

HI,

It’s unable to remove row from the iterating datatable.
As one of workarounds, we need to add datatow which is not match condition to another datatable as the following, for example. (Then copy it to original if necessary)

not( DateTime.Parse(row("columnX").ToString).Date=Today.AddDays(-1) AndAlso TimeSpan.Parse(row("columnY").ToString)<New TimeSpan(7,30,0))

Regards,

[nilesay]

Hi,

It can’t filter because it detects column y as double. first I will try to convert the y column to time format with another for each. How can I do that?

Best regards,

[nilesay]

Hi @Yoichi,

It can’t filter because it detects column y as double. first I will try to convert the y column to time format with another for each. How can I do that?

Best regards,

[Yoichi]

Hi,

Can you share your workbook as a file? It’s no problem if dummy data.

Regards,

[nilesay]

file1.xlsx (32.8 KB)

[Yoichi]

Hi,

@nilesay

column y as double

Do you use ExcelApplicationScope and ReadRange? Sometimes, it returns serial value of excel.
In this case, the following condition will work. Can you try this?

not( DateTime.Parse(row("X").ToString).Date=Today.AddDays(-1) AndAlso DateTime.FromOADate(Double.Parse(row("Y").ToString))-DateTime.FromOADate(Double.Parse((row("Y").ToString))).Date<New TimeSpan(7,30,0))

FYI, the following is sample with Workbook-ReadRange.

Sample20220913-6.zip (26.9 KB)

Regards,

[nilesay]

Hi @yoichi,

When I run, the following lines are written in the result excel.But when I filter the x and y column manually there are only 5 rows that match the conditions.

[Yoichi]

Hi,

I want to delete rows that match the value equal to yesterday’s date in column x and less than 7:30 in column y

There are 5 rows which match your condition and they are deleted. i think it’s same with your requirement.

Regards,

[nilesay]

Sorry @Yoichi, you’re right. Thank you.

[nilesay]

Hi @Yoichi ,

The excel file I am filtering changes daily. Excel contents are the same but I am getting the following error. In which format do I need to fix the X and Y columns?

Best regards,

“If: String was not recognized as a valid DateTime.”

[Yoichi]

Hi,

“If: String was not recognized as a valid DateTime.”

This error shows there is non-datetime string or empty string. Can you try to check if they are valid datetime string in advance, as the following?

Sample20220913-6v2.zip (27.2 KB)

Regards,

[nilesay]

Hİ,

I tried and it returned all lines ‘not valid datetime string’. Column X is ‘date’ and column Y is of type ‘custom’ before running. Should I change it to another format?

Regards,

[Yoichi]

Hi,

As I cannot get your situation perfectly, can you share screenshot of error message etc?

Regards,

[nilesay]

All rows return ‘not valid datetime string’.

[Yoichi]

Hi,

As input string is dd.MM.yyyy style, we need to modify expression for columnX as the following.

Sample20220913-6v3.zip (27.2 KB)

Regards,

[nilesay]

Hi,
Still return ‘not valid datetime string’. But format of the X column has changed.

Best regards,

[Yoichi]

Hi,

As columnX’s format is changed, the above sample v2 will work, I think. Can you try it?

Regards,