But I am having a hard time getting it to work when I try to convert it so that:
It references the column by index rather than by name
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)
Try this one
io_TransactionData.Rows.IndexOf(
io_TransactionData.AsEnumerable.First(Function(x) x(0).ToString.Trim = “”)
)
Returns index of First blank row
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.
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.
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
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
@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!