How to find the end row (dynamically) in any column in a DT

Referring to this thread:

can we change the formula (as mentioned by @ClaytonM) to incorporate the column header name dynamically :slight_smile:

( dt1.Rows.IndexOf( dt1.AsEnumerable.Where(Function® r(“Fri”).ToString.Trim <> “”).ToArray.Last ) + 2 ).ToString

I’m using a For Each for rows inside Do-while,to change the columns (& rows) dynamically, but the program stops when encounters a column with less rows than the largest column (as in the attached link).

Sorry for a late response on your question.

I think what you are asking is “can you assign each column in each row?”

For this, I would suggest interacting with the data as a Data Table, rather than using Write Cell. This way you can use a For each on the columns (Note: you can also use the Index property rather than using .Rows.IndexOf()

Here is an example in psuedocode:

For each col In dt1.Columns //TypeArgument DataColumn
    For each row In dt1
        Assign: row(col.ColumnName) = newvalue

Then, Write Range to output the updated data.

If dt1.Columns has an error, you might need to cast it like dt1.Columns.Cast(Of DataColumn).ToArray

If you have a specific thing you are needing to do and need further help, please provide some more details, like image of your code-snippet.


thanks for your reply, i was having problems due to presence of empty cells in a columns.

All the other values were used to ‘find’ a value on a webpage and select the corresponding check-box. Now, with the presence of empty cells, it was selecting a ‘Null’ value and getting nothing in return and hence the error.

Since, the reply came in a little late, what i did was to fill the empty cells with a value which was already there (a static text on the webpage) and let bot click it instead. Worked for me.

Kinda befriended the enemy haha!

1 Like