Copy a the last row of excel from from a specific range

Hi,

I want to copy the last row of excel from range U to AA only and then paste it to the next empty row (from range U to AA).

Please help i used read range activity with the help of this i got the last row but i want it within a specific range.

Hi @nsharma,

You can specify range in the read range activity from whatever range you want. mention the range as “U” + lastRowIndex.tostring + “:AA” + lastRowIndex.tostring.

Then it will read only that particular and you can do whatever you want…

And Hope someone will need this to convert the column names (after “Z”) to the index. Here is the code

       Dim colsCount As Integer
        Dim k As Integer
        Dim rangeString As String
        colsCount = ws.UsedRange.Columns.Count
        For k = 0 To colsCount

            If k > 25 And k < colsCount Then
                rangeString = Convert.ToChar(65).ToString + Convert.ToChar(65 + k - 26)
            Else
                rangeString = Convert.ToChar(65 + k)
            End If
1 Like

Thanks, Can you please suggest how would i write it to the next row in the same range?

You can mention the range in write range activity as well. You will have lastRowIndex and mention the write range as
“U” + (lastRowIndex + 1 ).tostring + “:AA” + (lastRowIndex + 1).tostring [If you want to write under the same columns].

1 Like

It worked thanks a lot, one more thing if i want to read an excel right after 4 rows.
How would i do this?

Same thing you can implement… You can mention the range whatever you need as

“A4:Z9000”

and if you have dynamic count, and you need to use the count variable in range :slight_smile:
“A4:Z”+count.toString

No i want to take data from A8 till the data is empty, means the last range is dynamic which is not fixed.

Hi @nsharma
You can take the count in assign Using Datatable,Rows.Count, and then specify the Range in Write Range as “A:Z”+Count.tostring

Thanks& Regards
Jitendra Ughade

1 Like

So you have to read the count of the datatable (lets say dt)

Lets say count is the variable and you can use assign activity as

count = dt.rows.count

I tried this “U”+lastRowIndex.tostring +“AA”+lastRowIndex.tostring

it is showing me error that “lastrowindex” is not declared.

lastRowIndex is the variable you need to declare and you have to assign a value using the assign activity .

lastRowIndex = dt.rows.count

then it will have the value of number of rows in your data sheet

After doing the same code it is throwing an error at the time of run that the read range is not exist.Flowchart.xaml (14.6 KB)

Your file is corrupted. It is not opening. Can you Zip it and attach.

Sorry it worked
i did not mention : for range

thanks a lot :slight_smile:

Hi Sorry,
The requirement is different with this code i am able to copy data but i want to copy and paste the data along with vlookup formula which so that after pasting it will populate the data accordingly.

Here is the code to copy and paste the data from one sheet to another. You have to change the variables according to your workflow

ws = CType(wb.Sheets(“Sheet1”), Microsoft.Office.Interop.Excel.Worksheet)
ws.Activate()
ws.Range(rangeString + “1:” + rangeString + countrows.ToString).Copy()
ws = CType(wb.Sheets(“Sheet2”), Microsoft.Office.Interop.Excel.Worksheet)
ws.Activate()
ws.Range(“A1:D” + countrows.ToString).PasteSpecial()

make changes in the code

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