Get last row without read range activity

[roberto.piccolli]

Hi everyone!

I’m facing an issue when trying to get the last row of an Excel file. Due to the size of the file and the fact that the columns aren’t always the same, the read range activity isn’t an option. Also the find last row activity doesn’t work.

As a workaround I’ve got a piece of VBA code to determinatet he last row of the file:
Dim lastRow as Long
lastRow = Sheets(“Open lines”).Range(“K” & Rows.Count).End(xlUp).Row

In the Invoke VBA activity I aad a variable for the output:

This output is automatically created as an Object variable. The issue is that I can’t manage to read this value as it returns just blank (no value):

How can I read the value of this object variable? Or is there any way to get the last row without reading range?

Many thanks,
Roberto

[Ashish_Soni]

Hi @roberto.piccolli ,

=ROW(INDEX(A:A,MAX(IF(A:A<>"",ROW(A:A)))))

Here, replace “A” with the column letter of the column you want to find the last row number for.

This formula works by finding the maximum row number for cells that are not empty in the specified column. The INDEX function returns the value of the cell at that row number, and the ROW function returns the row number of that cell. This gives you the last row number of data in the specified column.

Thanks

[Palaniyappan (Palaniyappan P)]

Hi

I think you are getting output as a Datarow

So if you want to convert a Datarow to a string try mentioning like this

String.join(“,”,Row.itemArray)

Cheers @roberto.piccolli

[roberto.piccolli]

Hi @Palaniyappan,
Could you explain the way you built the expression? It didn’t work for me.
String.join(“,”,MyObjectVariable)

The output is the same as before:

@Ashish_Soni Where would you write the expression? The issue I have is that I can’t read range for thisp art of the process.

Best regrads,
Roberto

[Ashish_Soni]

Hi @roberto.piccolli ,

Are you sure you have the data in the column ‘K’

Regards

[roberto.piccolli]

Hi @Ashish_Soni

Yes, there’s data in the given column:

Best regrads,
Roberto

[Ashish_Soni]

Hi @roberto.piccolli ,

Lets try assign acitivity

strNewVar = lastRow.ToString()

then try the message box for strNeVar

Thanks

[roberto.piccolli]

Hi @Ashish_Soni,

It says that object reference not set to an instance of an object.

Is there maybe any other way I could get the last row without reading range?

Best regrads,
Roberto

[LAKSHMI_NARAYANA_PEMMASAN (LAKSHMI NARAYANA)]

is their any issue in using oledb

excel as database , make query to get the row count

Regards

[roberto.piccolli]

HI @LAKSHMI_NARAYANA_PEMMASAN

The problem on using it as data table is the difference in the amount of data as it’s not structured.
So the process can sometimes read range but it’s not a robustic solution as it also tends to fail.

The solution then would be to get the data without using the read range activity. But here is where I’m facing the issue. And since I have multiple files (about 500) it wouldn’t be a smooth solution to link all the files to Excel.

Best regards,
Roberto

[Ashish_Soni]

Hi @roberto.piccolli ,

Sheets(“Sheet1”).Range(“A1”).End(xlDown).Address

Please use this expression.

Thanks

[pratik.pandya]

@roberto.piccolli

Try these step if you want the Data of Last Row.

1. You can read whole data table using read Range Activity.
2. Use Assign Activity and Assign lv_LastRow_Index= YourDT.RowCount-1
3. Use another Assign Activity and Assign Lv_LastRow_value= YourDT.Rows(lv_LastRow_Index)(YourColumnName) .

That’s how you can get the Last Row Value for the Column you want.

Regards

[Anil_G (Anil Gorthi)]

@roberto.piccolli

Please try this…this will give the count of rows directly

Hope this helps

cheers

[roberto.piccolli]

Hi @Anil_G
The solution works perfectly and in the way I need it to be.
Many thanks.