Remove filter from excel sheet

By default, the input excels sheet filter is used. How to remove it?


TIA

@Cuberoot

You can use vba to remove it

Sub Filter()
    if ActiveSheet.AutoFilterMode then
       Cells.AutoFilter
    end if
End Sub

Save in text file as vba and use invoke vba

cheers

1 Like

what should i write in method name?

@Cuberoot

Filter is what you need to write

cheers

1 Like

Hey

This is what you need to perform to import the file that @Anil_G said

Regards

Hi @Cuberoot ,

Take a look on this.

Regards
Balamurugan.S

1 Like

HI @Cuberoot
If you have the header selected with filter on, alt+down will show the Filter options so you can navigate with keystrokes. I wonder if you can do that and look for the Search input box or something to check if Filter is on, cause if it can’t find it then it’s not on.

or
If the filter is ON, when you send Ctrl+Shift+L, the filters are turned off.
If the filter is OFF, when you send Ctrl+Shift+L, Excel will try to turn on filter for whatever cells you have selected at the time.

Try both the ways
Regards
Mohini
HAppy Automation.!!!

thnx it worked but I have one issue ,when bot copy the files before ‘filter macro activity’.
it copy like this:

but after using macro,it copy liked this:


how to hide this yellow part?
TIA

@Cuberoot

If you remove filters then all lines will be visible right…Again either you need to apply filter for removing them…

cheers

ok. thanks for your reply

In the excel sheet, there is a filter of availability rows=0.So How to copy this sheet?

@Cuberoot

Can you be a little more clear ?

cheers

in 1 sheet they have added a filter i.e if row=1 it will hide and remaining data will show.Afetr copying the data,I want to hide row=1

@Cuberoot

Did you try using copysheet activity instead? Then filters also get copied

Else we have to write another macro to add the filter again

cheers

1 Like

Could you please share the macro for the filter? It will be beneficial for me.FYI, in the excel sheet, some rows of data have been written and some are empty rows. For empty rows, they mentioned 1 and for not empty rows they mentioned 0. The filter is used for not-empty data rows. How to copy not empty rows?
TIA

@Cuberoot

Can you please share the file

Cheers

@Cuberoot

can you please send how the output should look as well I mean what filter wxactly to apply

Cheers

it should deselct =1 from the filters(avail rows)
hills.xlsx (44.0 KB)

@Cuberoot

Please try this to filter on that file

Sub Filter()
    If ActiveSheet.AutoFilterMode Then
       Cells.AutoFilter
    End If
    ActiveSheet.Range("A1").AutoFilter Field:=30, Criteria1:="<>1", Operator:=xlFilterValues
End Sub

If you want to apply for other fields then Field:=Value this value should be changed to the respective column number you need filter on…Here I am filtering the value 1 out of the values

cheers

not working.Have you tested it?