Get first blank row index

Help
,
1

How do I get the index of the first blank row in an excel file that I’m reading into a data table?

I am able to return the index of a column “Status” that contains the value “New” with this formula:

io_TransactionData.Rows.IndexOf(io_TransactionData.AsEnumerable().Where(Function(row) row("Status").ToString.Trim = "New").ToList().First)

But I am having a hard time getting it to work when I try to convert it so that:

  1. It references the column by index rather than by name
  2. It returns only values that are blanks (would ideally like to use the string.isNullOrWhitespace function)

Can anyone please tell me what I’m doing wrong?
io_TransactionData.Rows.IndexOf(io_TransactionData.AsEnumerable().Where(Function(row) row(0).ToString.Trim = "").ToList().First)

2

Try this one
io_TransactionData.Rows.IndexOf(
io_TransactionData.AsEnumerable.First(Function(x) x(0).ToString.Trim = “”)
)
Returns index of First blank row

1 Like
3

Thank you! Just tried and it gives me this error message that sequence contained no matching element, which is pretty odd since the Excel file I am reading definitely contains blank rows.

image
image1311×546 93.9 KB

4

Column index is 0, isn’t it?
Can you show your datatable?

5

@oliviamga2

find some starter help here (demo on find first, last, all index for a blank column)
FindIndex_FirstLastAllMatch_1Col_1Val.xaml (8.7 KB)

6

TempOutput.xlsx (9.4 KB)

Here’s a modified version of the file I am working with. In reality, it has over 26 columns, but I doubt that would make a difference in how the rows are read.

Since I’ll be adding data in batches to the file, I want to be able to locate the index of the first blank or empty cell in the first column and then use that index to write data to various columns.

Thank you for your help!

7

Thank you @ppr!

These formulas are awesome. The only problem I ran into is that while they work great to return the index of a column cell that contains a particular phrase, they do not work for blanks, which is my goal.

I tried modifying the first formula as follows:
DTTemp.AsEnumerable.toList.FindIndex(Function (r) r(0).ToString.Equals("")) + 2

But the result is always “1”, although that is not the correct index to return since I set up the test file to have its first blank cell in column A7.

I suppose I could use the formula to find the index of the last cell that contains values (adding 1 to bring me to the next, blank cell), but the problem there is that I can’t control what text will be present in the previous cells and I have not had any better luck using wildcards

8

Does Row 7 contain any data for any column?
If not, it automatically reads A1:J6 for your sample File.
So A7(row 7) is not contained in your datatable, upper solutions return that your Datatable doesn’t contain blank rows

9

@oliviamga2
if find index doesnt find the match it will return -1. So with adding +2 on this we got 1.
Does mean: you got 1 as it was not found.

For Blanks give a try on following:

dtData.AsEnumerable.ToList.FindIndex(Function (r) (isNothing(r(0)) OrElse String.IsNullOrWhiteSpace(r(0).toString.Trim)))
3 Likes
10

Duh. I should have thought of this. This is exactly what is happening with my data set, and makes perfect sense now.

As a workaround, I will use row count to return the number of rows in the data table + 1 and use that for the next row index to fill with data.

Thank you so much for your help, functions are something I want to grow stronger in and this thread taught me a lot!

11

@ppr THANK YOU! This is exactly the sort of function I was struggling to create. It works like a charm and I’ll be using it quite a bit in the future. Many thanks!

12

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