Count rows when field is null until non null is found

I need to count the rows in a datatable when a certain field is null until data is presented. My thought is a Do While For Each Row and the Condition to kick out is the certain field is Null. Struggling to write the logic to count null rows and the condition to end the do while. Any help is greatly appreciated - I’m a total rookie here.

Thanks!

Hi @Vantreat,

Please check filter datatable activity.
Let me know in case of any queries.

Thanks,
Amol

can you please confirm why are you using Do while.

Try something like this…


image

I have a system generated report that has “junk header” rows at the top of the file. The junk headers always only have data in the first two fields so I’m finding the last field in the workbook and trying to identify where the first row of data is to define the true header row. My thought is if I can count the rows until I find the row with data in the last field that defines the header.

1 Like

Hi
–hope this expression would help you on that
if we have obtained the datatable from the READ RANGE activity with a variable named dt
then this ASSIGN ACTIVITY will give us the row count
int_count = dt.Select("[yourcolumnname] = ‘’ ").Count
or
int_count = dt.Rows.Count - dt.Select("[yourcolumnname] <> ‘’ ").Count

Cheers @Vantreat

To add a bit more color to what I’m trying to accomplish. This is what the structure of the data table looks like.

Snag_53ce653

I’m trying to build a tool that allow for the dynamic removal of records 1-3. Or take A4:F8 and write it to a new table in the same sheet so there is a standardized format for use in other tools.

The challenge is every report has a different output in terms of the number of “Filters” but they are ALWAYS only in the first two fields. I also want various users to be able to use the tool as a bot with their own reports. So to this point I have accomplished the following:

  • Allow user to select their report
  • Get the sheet name (within the excel application scope) as they are different in some reports
  • Using the System Read Range to define the data table and then assign counts to the number of rows (rowCount) and fields (colCount) in that data table.
  • A sequence to find the last Column letter and store it for potential use (colLetter)

Thanks @Palaniyappan the field doesn’t have a column name but I know the position and the column number - how would I indicate that. I realize a very rookie question but I have tried several options and am stuck.