How to get the last row index of Column A excel via linq query

Help Activities
, ,
1

How to get the last row index of Column A excel via linq query
i want to get the last value index in Column A

2

@T_Y_Raju,

Use Read Range activity to load the excel data into Datatable.

This will give you last row index

intlastIndex = datatableVariable.RowCount-1

Thanks,
Ashok :slight_smile:

3

here i want to get only the coumn A last row because in my all the data is not same some columns 10 rows of data other have 15 like that

4

@T_Y_Raju,

Use this logic.

image
image924×646 26.4 KB

Input:
image

Output:
image

Sample Code:
Workflow1.xaml (10.6 KB)

Thanks,
Ashok :slight_smile:

5

Pls use the validation before using that you have the data in data table and you have the column name in that data table

var firstColumnValue = dataTable.AsEnumerable()
                                .LastOrDefault()?["coumn A"]?.ToString();

@T_Y_Raju Hope it solve your issue

if you wants to remove Empty row or cell pls use below one

 DataTable dataTable = new DataTable();  
            var firstColumnValue = dataTable.AsEnumerable()
                                 .Where(row => !string.IsNullOrEmpty(row["ColumnName"]?.ToString()))  // Filter empty rows
                                 .LastOrDefault()?["ColumnName"]?.ToString();

If you have empty space you wants to remove then use below

var firstColumnValue = dataTable.AsEnumerable()
                                 .Where(row => !string.IsNullOrWhiteSpace(row["ColumnName"]?.ToString()))  // Filter empty or whitespace rows
                                 .LastOrDefault()?["ColumnName"]?.ToString();

you need to do some modification according to your column

6

Counting how many rows aren’t blank, isn’t the same as getting the last row that isn’t blank.

Consider this data

image

Counting how many rows column A is not blank will give you 3, but the last row where column A has data is row 4.

7

Have you considered what happens if row 2 is blank for the Sample column in your example?

If you truly want the last row where Sample has data, you’ll want to For Each and check the value of each row. Each time you find a value, store the row index. Each time there is no value, do nothing. At the end you’ll have the row index of the last row where Sample has a value.

9

then how to i get the last row value if my data has empty cell but i dont want to consider that cell i want only the last bottom value of excel in col A

10

I explained what to do.

11

Hello @T_Y_Raju ,

Try to use

(From row In dtExcelData.AsEnumerable()
Where Not String.IsNullOrEmpty(row.Field(Of String)("Column1"))
Select dtExcelData.Rows.IndexOf(row)).LastOrDefault() + 1

In assign activity hope it will help you
Regards,
Dheerendra Vishwakarma

12

@T_Y_Raju

Try this activity and give A:A as range

Cheers