After my code has filtered on some columns, I delete the visible rows. This sometimes results in
no rows remaining
in the AutoFilter range. I then attempt to reset (i.e., clear) all the filtered columns (i.e., the ones with the funnel icon) using the Worksheets.ShowAllData method. However, this causes a Run-time error '1004': ShowAllData method
of Worksheet class failed.
Does anyone know of a work-around for ShowAllData when no rows remain in the AutoFilter range?
Thanks in advance for any assistance.
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another.
Any content of an adult theme or inappropriate to a community web site.
Any image, link, or discussion of nudity.
Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect.
Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software.
Unsolicited bulk mail or bulk advertising.
Any link to or advocacy of virus, spyware, malware, or phishing sites.
Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct.
Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
Hello,
ActiveSheet.ShowAllData only works if a filter applied, or it will give error message - "Run Time error 1004 - ShowAllData method of Worksheet class failed".
You may try following to give a reminder there is no filter applied.
Sub Makro2()
If ActiveSheet.FilterMode = False Then
MsgBox "No filter !"
ElseIf ActiveSheet.FilterMode = True Then
ActiveSheet.
ShowAllData
MsgBox "unknown case"
End If
Please remember to click “Mark as Answer” if this response helps you.
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another.
Any content of an adult theme or inappropriate to a community web site.
Any image, link, or discussion of nudity.
Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect.
Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software.
Unsolicited bulk mail or bulk advertising.
Any link to or advocacy of virus, spyware, malware, or phishing sites.
Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct.
Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
Thanks for your solution.
However, I was hoping someone could show me a way to clear all the filters (i.e., reset all filtered columns) when
no rows remain
in the AutoFilter range as a result of deleting all the filtered rows.
As long as (at least)
one row
remains in an AutoFilter'd range, then
ShowAllData works fine. It's when
no rows remain
that
ShowAllData causes the "
Run-time error 1004: ShowAllData method of
Worksheet class failed." message to be generated.
Harassment is any behavior intended to disturb or upset a person or group of people. Threats include any threat of suicide, violence, or harm to another.
Any content of an adult theme or inappropriate to a community web site.
Any image, link, or discussion of nudity.
Any behavior that is insulting, rude, vulgar, desecrating, or showing disrespect.
Any behavior that appears to violate End user license agreements, including providing product keys or links to pirated software.
Unsolicited bulk mail or bulk advertising.
Any link to or advocacy of virus, spyware, malware, or phishing sites.
Any other inappropriate content or behavior as defined by the Terms of Use or Code of Conduct.
Any image, link, or discussion related to child pornography, child nudity, or other child abuse or exploitation.
