Set filter off with VBA - not working...

Worf99

New Member
Joined
Jan 13, 2018
Messages
44
Hi to all,
I'm new here.

I'm having a hard time writing a VBA code to disable filters in a file.


I found this code

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">
Code:
</code>Sub AutoFiltroOff()<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">
Dim wb As Workbook
Dim ws As Worksheet

Set wb = ThisWorkbook
Set ws = wb.ActiveSheet

'azzera le condizioni del filtro automatico
'e mostra tutti i dati
If ws.AutoFilterMode Then
If ws.FilterMode Then
ws.ShowAllData
End If
End If

Set ws = Nothing
Set wb = Nothing

</code><code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit; line-height: 12px;">End Sub


but it works only on a simple file.

If I try it in the file I need to work with, it doesn't work.

I have this error (I have Excel in Italian language, so this error could be a little different in English)

"run time error 1004. 'ShowAllData' method of object '_Worksheet' failed".

What am I doing wrong?

Thank you.</code>
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Is the code in the same workbook as the sheet being filtered and is the sheet protected?
 
Last edited:
Upvote 0
Try
Code:
Sub AutoFiltroOff()
   Dim wb As Workbook
   Dim ws As Worksheet
   
   Set wb = ThisWorkbook
   Set ws = wb.ActiveSheet
   ws.Unprotect Password:="[COLOR=#ff0000]mypassword[/COLOR]"
   'azzera le condizioni del filtro automatico
   'e mostra tutti i dati
   If ws.AutoFilterMode Then
      If ws.FilterMode Then
         ws.ShowAllData
      End If
   End If
   ws.Protect Password:="[COLOR=#ff0000]mypassword[/COLOR]"
   Set ws = Nothing
   Set wb = Nothing

End Sub
Changing the 2 values in red to match your password
 
Upvote 0
Thank you, thank you, thank you!!!!
It worked!!!!
I just added AllowFiltering:=True to let me use the autofilter!
 
Upvote 0
Glad we could help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top