How to delete a last row in excel file when the data is dynamic?

Hi All,

How can we del the last row of an excel in whic the data is dynamic.

For ex: A report having 10rows today will have 13 tomorrow.

How can we design the workflow to detect the last row for the sheet?

Hi @Vishal_Singh1
to remove the last row u can use Remove DataRow Activity

after reading excel and storing in dt1 variable, then use dt1.Rows.Count-1 in the RowIndex of Remove datarow activity

It will remove the last row even if the number of rows is dynamic.

Regards,

Nived N
Happy Automation

@Vishal_Singh1
for doing it directly on excel following will help:
retrieving the index of last row:
grafik

deleting the row with the index:
grafik

Ommiting the last row on datatable base we can do like following:
dtShorted = dtOrigVar.AsEnumerable.Take(dtOrigVar-Rows.Count - 1).CopyToDataTable

Hi @Vishal_Singh1

Read excel and Store and it in datatable (variable name dtTable)

And in assign give

dtTable = dtTable.asenumerable.reverse.copytodatatable.asenumerable.skip(1).copytodatatable.asenumerable.reverse.copytodatatable

And then use write range and pass the sheet name and datatable (dtTable)

Or

You can use invoke VBA activity and find LastRow and delete the LastRow entirely

Sub macro()

Sheets(“yoursheetname”).select
Lrow = cells(rows.count,1).end(xlup).row
Range(“A” & Lrow).entirerow.delete

End sub

Thanks

1 Like

Hi @NIVED_NAMBIAR ,

Could you please show me on the workflow?
after the file opens how to design it?

here is mine:

Hi @Vishal_Singh1

Inside excel application scope, use read range. In the output variable you will get one data table.

Then use “Remove data row” activity, in the properties of Row index use yourdatatable.Rows.Count-1
This will delete last row.

Thanks

Hi @Vishal_Singh1

attaching a sample workflow for better understanding …
sample.xaml (6.2 KB)

Regards,

Nived N
Happy Automation