Delete certain rows and keep formulas in excel

Help
1

Hi. I have excel table that has multiple rows and columns. And I would like to keep rows which has “United Kingdon” in column Z and remove all other rows. And then I would like to overrite changes, save and close workbook.

Please see attached file. I used different scenarios as “filter data row” and “remove data row” and “invoke method”, however when I use these activities I lost formulas (getting from next page) in column AA, column AB, etc.

How can I do:
1- keep rows which has “United Kingdon” in column Z, and delete remaining rows
2- Overrite changes in the excel by keeping formulas
3-Save & close same excel file

RemoveRows.zip (541.0 KB)

2

Hi @DD38

Welcome to uipath commuity
we can use Filter datatable activity for this buddy
https://activities.uipath.com/docs/filter-data-table
like this
but put your datatable name you have in your workflow

image
image1283×462 25.8 KB

cheers @DD38

3

Hi @Palaniyappan
Thanks for your prompt response.

I also used filter data table as attached one. However,

  • I am loosing formula in highlighted columns (should be kept with formulas)
  • How can I keep also second page named “Reference”?
  • I also would like to overrite changes. However, in my bot I am creating new excel file due to write range.

Datafilter.zip (548.9 KB)

4

@DD38 Welcome to the community! Please see attached a sample workflow to delete rows that do not have “United Kingdom” in column Z and keeps the formula in the remaining cells. It uses Read Column and Insert/Delete Rows activities.

Sample.xaml (9.6 KB)

5

Hi @Palaniyappan,

It there a way to delete the rows by applying filter in excel sheet in uipath.

We can perform this using Macro. But would like to eliminate macro in this

Thanks,
Muthuraj