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?
Have you try with Read Range activity
This expression will get the last row of the DataTable
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.
In my opinion i think without reading the excel file, We can’t able to get the row count @Emmy_O
Use the above link as a reference
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.
Checkout this thread
hope this helps
Check this post
Contains same requirement
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.
I have found an easy way to achieve this with the VBA code that I found online:
Dim LR As Long*
'For understanding LR = Last Row*
LR = Range(“A:L”).SpecialCells(xlCellTypeLastCell).Row*
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
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.
@Emmy_O , Check the below post :
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.
@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.
@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 ?
I was trying Append Range Activity from the modern design
For this, one need to define the range.
Here is my VBA code:
Dim LR As Long
'For understanding LR = Last Row
LR = Range(“A:L”).SpecialCells(xlCellTypeLastCell).Row
The correct output was generated by the MsgBox
but the output variable I created in UiPath returned no result
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 ?
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!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.