Delete Filters And Cell

Help
, ,
1

Hello everyone,

I work on a process.
I need to copy a previous version of a xlsx file, delete some cells (on many sheets) and copy new value in it.

But when I copy my old version, some users have left filters, and prevent me from correctly deleting my old values before putting new ones in.

Do you have a solution to remove all the filters? (There are more than 10 sheets, so not a solution to do it with clicks)

Thanks everyone :sunglasses:

2

You can invoke macro to turn off all filters from all sheets.

For Each sheet in Sheets
set filter to OFF

Regards,
Karthik Byggari

1 Like
4

Itā€™s a protected file, and I donā€™t have access to the macro, etc. I can still enter values inside, and click on the column to manually remove the filters.

With the execution of the macro, I am required to be an administrator of the file, right?

5

Hi
we can use EXCEL APPLICATION SCOPE AND a READ RANGE ACTIVITY where if the option use filter is disbaled it wont take the filter even if it is applied
image

and then to delete cell value we do that with normal assign activity like this
this will delete the particular cell value
Yourdatatable.RowS(rowindex)(ā€œcolumnnameā€) = string.Empty
where rowindex usually starts from 0 for the first row

this will enter new value if we want
Yourdatatable.RowS(rowindex)(ā€œcolumnnameā€) = ā€œnew valueā€

kindly try this and let know for any queries or clarification
Cheers @Zeynk

6

Thanks , Iā€™ll try.
But why doesnā€™t a Delete range technically work? Knowing that weā€™re giving the range, but doesnā€™t take effect if thereā€™s a filter?

Thanks for the help :wink:

1 Like
7

As it is in protected mode.
May be I m not we need to try once with that file
But fetching the data from datatable format would be robust always
Cheers @Zeynk

8

Iā€™ll try the method.
But I think that in terms of optimization, Iā€™m still going to waste a lot of time modifying one by one.
Thatā€™s 500 rows, about to be emptied of 15 columns.

Thanks again :wink:

1 Like
9

To optimize,

Use read range activity to read the data into a data table.
Modify the data table as per your needs
And then write the data table to the excel.

Regards,
Karthik Byggari

10

Thatā€™s fine buddy
Read range from excel activities can handle that much rows and columns
Kindly try and let know for any queries or clarification

Cheers @Zeynk

11

Hey, Iā€™m back.

I used the technique (although it is relatively long to empty each cell). Once the values are replaced after Yourdatatable.RowS(rowindex)(ā€œcolumnnameā€) = string.Empty, I have to Write Cell the datatable in the Excel file?
If so, it unfortunately does not work. :cry:

1 Like
12

No not in write cell buddy
With a single assign activity having this expression

Yourdatatable.Rows(rowindex)(ā€œcolumnnameā€) = ā€œnew valueā€

Cheers @Zeynk

14

Hey buddy,

I Understand, but itā€™s modify only the DataTable. I want to clear the Excel file too. :slightly_smiling_face:

EDIT : I notice that I hadnā€™t reported that I also wanted to remove the content of the Excel page in the filter.

15

Fine this expression would have changed in the datatable and if this is written back to excel with write range activity then it would be updated In excel as well

Cheers @Zeynk

16

Unfortunately, only the ā€œfilteredā€ values are changed. The entire excel array hidden from him under the filter, is not modifiedā€¦ :frowning:

17

Can you use hotkeys instead of clicks or macros?

Ctrl + Shift + L to turn on/off filtering (if filtering is off, you can go to a stray cell and try to turn it on and it will error with a message that ā€œThis canā€™t be applied to the specified range. Select a single cell in a range and try again.ā€)

Ctrl + PgUp / PgDn to switch between worksheets

18

Hey Kristopher,

Iā€™ve already tried.
The file is protected and I donā€™t have control with the shortcut.

19

Can you create a new file instead of editing the old one? Then you can copy the records out of the original file into the new file that you have full control over.

20

No I Canā€™t , I really need to use this file :confused:

21

So if you have to use the same spreadsheet, can you just click on the Enable Content since it is protected and then use the shortcuts on each worksheet?

And why do you have to use the same spreadsheet?

22

@Palaniyappan Hola, espero estƩs bien, te escribƭ al correo que tienes registrado.