Find last row with data in Excel

[Emmy_O (Emmanuel)]

Hello everyone,
Please, I need help in finding the last row with data in an Excel sheet. I am writing some values into my excel sheet dynamically, and need to determine the last empty row so as to append the new values.
I have tried the “Find First/Last Data Row” actitivity but this does not work in my case because the number of rows in each cell are not always equal. For instance, column A might have 10 rows and column B has just 5 and vice versa. The “Find First/Last Data Row” actitivity only works by selecting a single column but I need to find the last row with data in the entire sheet. Please, does anyone know how to achieve this?
Many thanks!

[Gokul001 (Gokul Balaji)]

Hi @Emmy_O

Have you try with Read Range activity

 DT.Rows.Count.Tostring

This expression will get the last row of the DataTable

Regards
Gokul

[Emmy_O (Emmanuel)]

Hello @Gokul001
Many thanks for your response. I don’t want to read the range into datatable everytime before writing the new values. I am getting this data from the web and saving into a datatable and then need to write to excel to generate a final output. So, I cannot read this excel sheet everytime to determine Row.Count.

[Gokul001 (Gokul Balaji)]

In my opinion i think without reading the excel file, We can’t able to get the row count @Emmy_O

Regards
Gokul

[RK_0 (RK)]

Find First/Last Data Row

Hi

Use the above link as a reference

Regards,

[Emmy_O (Emmanuel)]

Hi @RK_S ,
I have tried this activity, but it only works for a single cell. You need to define the spicific cell for which you need to get the last row. But in my case, I need to find the last row with data across the sheet.

[Sudharsan_Ka]

Hi @Emmy_O

Checkout this thread

UiPath Go ! BalaReva EasyExcel Activities

Hi , I have developed 2 new activities. Find LastColumn that to find the last column Index and Column Name of the sheet . Find LastRow that to find the last row of the sheet and it returns the row index. [image] [image] Find LastColumn’s Properties : [image] Find LastRow Properties : [image] Note : If the sheet name is empty , It will take the first sheet name . Here is the video demonstration. [UiPath excel Extract Graph as Image | find last column Index| find last Row No…

hope this helps

Regards
Sudharsan

[RK_0 (RK)]

Hi,

Check this post

How do I find the last row?

Hi @Perawat Please check the workflow attached, Rowcount_Excel.zip (24.9 KB) or Please check this post might help you, Thanks Latika

Contains same requirement

[supermanPunch (Arpan)]

@Emmy_O

I don’t want to read the range into datatable everytime before writing the new values.

Hi @Emmy_O ,

If the data already exists in the excel sheet, you could try using the Append Range Activity to append the new row values at the end of the available values.

Could you give this a try and let us know if it is suitable for your case.

[Emmy_O (Emmanuel)]

I have found an easy way to achieve this with the VBA code that I found online:

Sub sblastRowOfASheet()

• Dim LR As Long*

• 'For understanding LR = Last Row*

• LR = Range(“A:L”).SpecialCells(xlCellTypeLastCell).Row*

• 'MsgBox LR*

End Sub

However, I am not sure how to save LR as a variable to be used in my code. Can any VBA expert help me with this? Thanks

[Emmy_O (Emmanuel)]

Thanks @Sudharsan_Ka , this is super helpfull. But I do not want to install another package since the BalaReva excel activities will not work within the existing Excel Application Scope. I have tried this and the only option for me would be to do everything within the BalaReva Excel activities, but this unfortunately does not have other avtivities that I need.

[supermanPunch (Arpan)]

@Emmy_O

I am not sure how to save LR as a variable to be used in my code

@Emmy_O , Check the below post :

Return variable value from invoked VBA code

[image] Properties [image] As shown in above link (Excel VBA Function Tutorial: Return, Call, Examples) I have done below sample code: [image] It is giving be output by bot [image]

We can use the function name itself for the assignment and get the output in the Invoke VBA Activity.

But I would also ask to give a try using the Append Range Activity to check if that can be an alternate/ would be a better approach if pasting the data at the end is all you need.

[Emmy_O (Emmanuel)]

@supermanPunch, with Append Range Activity, I need to specify the range I want to append data to. I do not know this cell value before hand and I need to determine it.

With the Invoke VBA Activity, I can see the correct value of my variable, LR being printed, but when I passed this variable as an output, it is returning no value. I don’t know what I am doing wrong.

This method perfectly solves my problem but I don’t know why I cannot output the variable from the code.
I am writing this data in a loop. I have an input excel sheet, and for every row in this sheet, I need to fetch some data from the web and write in another excel sheet which would be my output.

[supermanPunch (Arpan)]

@Emmy_O

Append Range Activity, I need to specify the range

@Emmy_O , There is no Range Property in the Append Range Activity.

As for the VBA execution, could you let us know what was the code used ? Do you get an error when executed ?

[Emmy_O (Emmanuel)]

@supermanPunch,
I was trying Append Range Activity from the modern design

For this, one need to define the range.

Here is my VBA code:
Sub sblastRowOfASheet()

Dim LR As Long
'For understanding LR = Last Row

LR = Range(“A:L”).SpecialCells(xlCellTypeLastCell).Row

MsgBox LR

End Sub

The correct output was generated by the MsgBox

but the output variable I created in UiPath returned no result

[supermanPunch (Arpan)]

@Emmy_O

Sub sblastRowOfASheet()

Dim LR As Long
'For understanding LR = Last Row

LR = Range(“A:L”).SpecialCells(xlCellTypeLastCell).Row

MsgBox LR

End Sub

@Emmy_O ,
Could you change the code to the below and check :

Private Function sblastRowOfASheet()

Dim LR As Long
'For understanding LR = Last Row

LR = Range("A:L").SpecialCells(xlCellTypeLastCell).Row

sblastRowOfASheet = LR

End Function

Let us know if it doesn’t work or gives out an error.

Also is switching/using Classic Excel activities not an option ?

[Emmy_O (Emmanuel)]

@supermanPunch,
Thanks a myriad! The VBA works now. I will also give it a try with the classic Append Range Activity and see if it works, and can then determine which method is most effective.
I really appreciate your help!