Find last row with data in Excel

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!

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

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.

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

Regards
Gokul

Hi

Use the above link as a reference

Regards,

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.

Hi @Emmy_O

Checkout this thread

hope this helps

Regards
Sudharsan

Hi,

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:

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

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.

image
image

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 ?

@supermanPunch,
I was trying Append Range Activity from the modern design
image
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
image
but the output variable I created in UiPath returned no result
image

@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 ?

@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!

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.