Get Index of First Empty Row


How do I get the Index for first Empty row? ex. I want index of row 6 from below screenshot.


givr a try on following activity for retrieving the last row with values:

Slight modification. Lets say I want to know Index of row 6 as shown below. how to do that.

give a try on:

yourDataTableVar.asEnumerable.toList.FindIndex(Function (x) x.ItemArray.All(Function (o) isNothing(o) OrElse String.IsNullOrEmpty(x.toString.Trim)))

Hi @tilarapiyush
After reading the excel and storing in datatable dt1

use the below assign activity to get row index of first blank column

blank_row_first=(From row In dt1.AsEnumerable
Where row.ItemArray.Where(Function(e) e.ToString.Equals(“”)).Count.ToString.Equals(row.ItemArray.Count.ToString)
Select dt1.Rows.IndexOf(row)).ToList(0).ToString

The blank_row_first will get the row index of first row which had blank data

Nived N
Happy Automation

With your DataTable in hands, assign:

rowIndex = Array.FindIndex(yourDataTable.AsEnumerable.ToArray, Function(row) row("Date").ToString = "")

In this case the result will be 4 (zero-based index). Add +1 to it if you need a one-based index.

Getting below error.

Result is showing up -1.

result is coming as -1.

try again with the added trim

Please check the reference:

Book1.xlsx (8.9 KB)
Main.xaml (8.7 KB)

If still having issues, share your excel workbook.

Hey, I rechecked. I am getting the correct answer as 4. But when I am trying same on below scenario, it throws -1 as result.

Hi @tilarapiyush
can u share the excel file please?


Its correct because you don’t have any empty cell in the whole datatable.

You can check if the result is -1, then your next empty row index will be yourDataTable.Rows.Count - 1.

Hi @tilarapiyush

did u tried my code for this excel file dataset?

Hey, I can only share the snapshots due to security purposes. :slight_smile:

Yes i did. It showed error as I sent.

Hi @tilarapiyush
can u try this modifled code
blank_row_first=(From row In dt1.AsEnumerable
Where row.ItemArray.Where(Function(e) e.ToString.Equals(" ")).Count.ToString.Equals(row.ItemArray.Count.ToString)
Select dt1.Rows.IndexOf(row)).ToList(0).ToString

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