Check for empty rows in excel where one cell in each row is predefined as "0.00"

Help
1

Hello!

I am trying to check for empty rows in a excel range by iterating the data table with a condition. The problem is that one of the cells in the row always contains “0.00” due to a calculation macro in that particular cell. So I’m trying to create a condition which first checks if the row is empty and also if that particular cell is not null or empty, like this:
image

The excel sheet contains several rows and it always work for the first one, but not the rest. I have tried several combinations in the condition but can’t seem to get it right.

Also when I output the data table the empty rows always turn out like this:
,0, The number of characters in this matches the number of columns I have in my DataTable.
Does this mean that every cell have a comma hidden in them? And the zero is the calculation of “0.00”?

How would I in this case manage to get the first empty row?

Regards

2

Can you share the workflow here with a sample excel data?

4

Sorry, can’t share due to confidentiality. But he calculation of cell with value “0.00” is like this: =A1-B1

5

Hi welcome to the community!
,0, means that first and third columns have empty (or null) values for that row.

7

Thank you! Do you know about the condition for sorting out these rows?

8

How is your excel formatted? That condition you want to do, will depend on your columns and the data you have on the first row…

9

Hi!

Here is an example how it can look.

image
image812×222 12.5 KB

Column 12 contains the calculation that I previously mentioned. When there is no value in that cell it contains "0.00. That is why the last row (4) in the picture never turns out like an empty row. I would like to fetch the first empty row with a condition that ignores the cell value of Column 12.

10

understood, so what do you consider an “empty row” that you want to skip? and the other condition is that column 12 is different than 0.00 right?

11

I consider an empty row as row 4, no values in any of the cells except from column 12 where it always Will be a calculation value. Thank you in advance!

12

but will you have empty rows in the middle of the data or only the final row? The best thing is to delete the last row so you dont have to check them… when you do the read range your data table should not have empty rows…

IF (String.Join("", row.ItemArray.Select(Function(p) p.ToString).ToArray).Lenght > 0 AND Convert.ToDouble(row("Column 12"))<>0.00)