【ExcelVBA】同一フォルダ内のオートフィルタを解除したい

Question

VBAが書かれているファイルと同階層にある、全てのエクセルファイルの全てのシートのオートフィルタを解除したいです。

初心者のため、手始めに「ファイル内の全てのシートのオートフィルタを解除（すべて表示）する」というコードを考えているのですが、

Sub すべて表示()
  Dim W As Worksheet
  For Each W In Worksheets
    ActiveSheet.ShowAllData
  Next W
End Sub

とすると、
実行時エラー1004
WorksheetクラスのShowAllDataメソッドが失敗しました

となってしまいます。
何がいけないのでしょうか？

merlionXX · Accepted Answer

merlionXXです。
これでどうでしょう？

Sub TEST01()
    Dim mb As Workbook
    Dim myfdr As String, fname As String
    Dim w As Worksheet
    Dim n As Long
    Application.ScreenUpdating = False '画面更新停止
    Set mb = ThisWorkbook 'このマクロがあるブックをmbとする。
    myfdr = ThisWorkbook.Path 'このブックのパス取得
    fname = Dir(myfdr & "\*.xls") 'フォルダ内のExcelブックを検索
    Do Until fname = Empty '全て検索
        If fname <> mb.Name Then 'ブック名がこのブックの名前でなければ
            Set wb = Workbooks.Open(myfdr & "\" & fname) 'そのブックを開きwbとする。
            For Each w In wb.Worksheets 'wb各シートに
                If w.FilterMode Then 'フィルターモードだったら
                w.ShowAllData 'オートフィルタをすべて表示
                End If
            Next w
            Application.DisplayAlerts = False '警告停止
            wb.Save '保存
            wb.Close '閉じる
            Application.DisplayAlerts = True '警告停止解除
            n = n + 1 'ブック数をカウント
        End If
        fname = Dir 'フォルダ内の次のExcelブックを検索
    Loop '繰り返す
    Application.ScreenUpdating = True '画面更新停止解除
    MsgBox n & "件のブックを ShowAllData しましました。"
End Sub

merlionXX · Answer

WがActiveにされていません。
ShowAllDataは、フィルターがかかっていない状態で作用させるとエラーになるのでエラー処理が必要です。

Sub すべて表示()
Dim W As Worksheet
For Each W In Worksheets
W.Activate
On Error Resume Next
ActiveSheet.ShowAllData
On Error GoTo 0
Next W
End Sub

xls88 · Answer

提示されたコードでは、同一ブック内の全ワークシートが操作対象です。
If文でAutoFilterModeを判定し処理をするようにしてみました。

Sub tes1()
　　Dim W As Worksheet
　　For Each W In Worksheets
　　　　If W.AutoFilterMode = True Then
　　　　　　On Error Resume Next
　　　　　　W.ShowAllData
　　　　　　On Error GoTo 0
　　　　End If
　　Next W
End Sub

n-jun · Answer

ShowAllDataのエラーを回避したい
http://oshiete1.goo.ne.jp/qa1619973.html

過去ログもご参考に。

merlionXX · Answer

No1のmerlionXXです。ご希望は「VBAが書かれているファイルと同階層にある、全てのエクセルファイルの全てのシートのオートフィルタを解除」なんでしたね。では、こんなコードではいかがでしょう？ Sub TEST01() Dim mb As Workbook Dim myfdr As String, fname As String Dim W As Worksheet Dim n As Long Application.ScreenUpdating = False '画面更新停止 Set mb = ThisWorkbook 'このマクロがあるブックをmbとする。 myfdr = ThisWorkbook.Path 'このブックのパス取得 fname = Dir(myfdr & "\*.xls") 'フォルダ内のExcelブックを検索 Do Until fname = Empty '全て検索 If fname <> mb.Name Then 'ブック名がこのブックの名前でなければ Set wb = Workbooks.Open(myfdr & "\" & fname) 'そのブックを開きwbとする。 For Each W In wb.Worksheets 'wb各シートに On Error Resume Next W.ShowAllData 'オートフィルタをすべて表示 On Error GoTo 0 Next W Application.DisplayAlerts = False '警告停止 wb.SaveAs Filename:=fname '保存 wb.Close '閉じる Application.DisplayAlerts = True '警告停止解除 n = n + 1 'ブック数をカウント End If fname = Dir 'フォルダ内の次のExcelブックを検索 Loop '繰り返す Application.ScreenUpdating = True '画面更新停止解除 MsgBox n & "件のブックを ShowAllData しましました。" End Sub もし、「解除」の意味が、ShowAllDataではなく、フィルタ自体をやめにしたいのでしたら、 Sub TEST02() Dim mb As Workbook Dim myfdr As String, fname As String Dim W As Worksheet Dim n As Long Application.ScreenUpdating = False '画面更新停止 Set mb = ThisWorkbook 'このマクロがあるブックをmbとする。 myfdr = ThisWorkbook.Path 'このブックのパス取得 fname = Dir(myfdr & "\*.xls") 'フォルダ内のExcelブックを検索 Do Until fname = Empty '全て検索 If fname <> mb.Name Then 'ブック名がこのブックの名前でなければ Set wb = Workbooks.Open(myfdr & "\" & fname) 'そのブックを開きwbとする。 For Each W In wb.Worksheets 'wb各シートに On Error Resume Next W.AutoFilterMode = False 'オートフィルタをやめる On Error GoTo 0 Next W Application.DisplayAlerts = False '警告停止 wb.SaveAs Filename:=fname '保存 wb.Close '閉じる Application.DisplayAlerts = True '警告停止解除 n = n + 1 'ブック数をカウント End If fname = Dir 'フォルダ内の次のExcelブックを検索 Loop '繰り返す Application.ScreenUpdating = True '画面更新停止解除 MsgBox n & "件のブックのオートフィルターをなくしました。" End Sub

Wendy02 · Answer

こんにちは。

>手始めに「ファイル内の全てのシートのオートフィルタを解除（すべて表示）する」

質問内容では、どちらかはっきりしません。

Sub すべて表示1()
'オートフィルタを解除
　　Dim wh As Worksheet
　　For Each wh In Worksheets
　　　　If Not wh.AutoFilter Is Nothing Then
　　　　　　wh.AutoFilterMode = False
　　　　End If
　　Next wh
End Sub

Sub すべて表示2()
'すべてを表示
　　Dim wh As Worksheet
　　For Each wh In Worksheets
　　　　If wh.FilterMode Then
　　　　　　wh.ShowAllData
　　　　End If
　　Next wh
End Sub
　　

なお、暗黙のルールですが、通常、ループなどの変数は小文字にします。エラーの種類がはっきりしている時はに、On Error トラップは必要ありませんね。

merlionXX · Answer

merlionXXです。

> ここを書き換えたらなぜうまくいったのでしょう？

> If w.FilterMode Then 'フィルターモードだったら
> w.ShowAllData 'オートフィルタをすべて表示
> End If
ここは、wendy02さんの、「エラーの種類がはっきりしている時はに、On Error トラップは必要ありません」に応えたもので、今回のとは直接関係ありません。

> wb.Save '保存
問題はここでした。
初歩的なミスです。最初（No4）の回答の
SaveAs Filename:= 
だと、「名前をつけて保存」なのですが、ファイル名だけでパス（保存先）を指定しなかったのでどこか別のフォルダーに保存されたものと思います。（ファイルの名前でPC内を検索してみてください。）
そのためオリジナルのファイルは前のままなんだと思います。
「上書き保存」なら単にSaveだけで良かったんです。ごめんなさい。
　(o。_。)oペコッ

【ExcelVBA】同一フォルダ内のオートフィルタを解除したい

WがActiveにされていません。

提示されたコードでは、同一ブック内の全ワークシートが操作対象です。

ShowAllDataのエラーを回避したい

No1のmerlionXXです。

こんにちは。

merlionXXです。

merlionXXです。

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング