Identifying an Empty Cell across the Datatable


I have an excel file with huge data. I need to identify whether there is any empty cell.
In another word, I need to get the count of empty cell anywhere in the datatable.

Thank you in advance for any help you can provide.

Geet M

Do you want check row or column wise?

Any way will work, I just need to find if there any empty cell.

You can use LINQ query to get the empty count by column wise

For your reference


Hi @iamgeet ,

Here is the solution.
Below expression will output boolean True if there in no empty cells in Datatable and False when there is atleast 1 empty cell in entire Datatable.

Replace TestDt with Excel datatable and use this expression in if condition

(From x In TestDt.AsEnumerable
Let count=x.ItemArray.Where(Function(y) String.IsNullOrEmpty(y.tostring.trim)).Count
Select count).Sum(Function(r) r)=0

You can find the same in this link

1 Like

Yes, The below code worked for me.

It returns the total number of empty cell in the datatable.

TestDt.Rows.Cast(Of DataRow)().Where(Function(row) row.ItemArray.Any(Function(field) (TypeOf field Is System.DBNull))).Count()

Yes, the code you shared is also working.
Thank you.

I think the solution returns count of empty cells present in last row not the entire table. Please recheck the linq

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