エクセルＶＢＡ オートフィルタの選択を元に戻す

質問

エクセルのＶＢＡで、次のことはできるでしょうか。
ブックの中の３つのシートはオートフィルタが設定してあり、任意で操作し、検索に使っています。（オートフィルタを設定しないしーとが２つあります)

・別のシートにチェンジしたら、チェンジ前のシートがオートフィルタで特定の行だけを表示していたら、オートフィルタを　＜すべて＞　に戻して、消えていた行を全て表示させたいのです。（オートフィルタは次回にまた使うので、データ－フィルタ－オートフィルタでオートフィルタ自体を解除してしまうような状態にはしたくありません）

・同じく、上記のことをブックを閉じるときにも実行したいのです。

ちなみに、オートフィルタをかけてあるシートには、以下のコードがあります。

よろしくお願いします。

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
With Sheets("印刷")
.Range("E15:E17").Value = _
Application.Transpose(Cells(Target.Row, 7).Resize(, 3).Value)
.Range("AA16").Value = _
Cells(Target.Row, 10).Value
.Range("AQ16").Value = _
Cells(Target.Row, 11).Value
.Range("AX16").Value = _
Cells(Target.Row, 12).Value
End With
With Sheets("施設")
.Range("C2").Value = _
Cells(Target.Row, 10).Value
End With
Cancel = True
Sheets("施設").Select
End Sub

回答No.4

Wendy02 · ベストアンサー

こんにちは。#3のWendy02です。

何度も見直しましたが、今のコードにどこと言って問題があるようには思えないのですが。
変数は、Sub の行の下に置く、というぐらいですが、これは見かけのためだけで、それでトラブルはありません。

オートフィルタを、データ－フィルタ－オートフィルタで、一回、解除して、もう一度、オートフィルタを設置してみたらいかがですか？もしかしたら、かなり大きなファイルではありませんか？もし、そうだとするなら、メモリ上の問題になります。

後は、ステップモードで動作試験をしてもらう、という方法ぐらいか見当たりません。
F8 を一回押すごとに、進んでいきますから、それで、どう変化するか、ということです。

Private Sub test()
'ブックを閉じるとき
　Dim Wsh As Worksheet
　For Each Wsh In Worksheets(Array("名簿", "施設"))
　　With Wsh
　　　If .AutoFilterMode Then
　　　　If .FilterMode Then
　　　　　.ShowAllData
　　　　End If

　　　End If
　　End With
　Next
　'ThisWorkbook.Save
End Sub

それとも、#1さんのコードに、AutoFilterMode の判定のオプションをつけてみるか、どうかですね。

お礼

すみませんでした。私が、データは５行目以降に入れていて、その一つ上の「４行目」はわざと空白セルにしていました。（これは、転記先を簡単に空欄にするためだったのです）しかし、それによりオートフィルターが４行目で止まってしまったようです。

４行目は、全くの空白ではなく、一文字分スペースを入れました。

すると、すべてうまく作動しました。

本当に、私が至らないせいで、余計な確認をさせてしまいました。
お詫びいたします。

また、ありがとうございました。

回答No.3

Wendy02

#2 の訂正です。

閉じるときに、一旦、保存しないとだめでした。
開いた時に選択されたフィルタモードに戻っています。

Private Sub Workbook_BeforeClose(Cancel As Boolean)
'略
　Next
'ここに↓を入れてください。
　ThisWorkbook.Save
End Sub

補足

ありがとうございました。

ThisWorkbook に設定したのですが、オートフィルタがうまく動かなくなってしまいました。
オートフィルタの三角印をクリックしても、その列に記録しているテキストデータがプルダウンされずに、（すべて）（トップテン）（オプション）（空白のセル）（空白以外のセル）だけが表示されるようになってしまいました。
私の方法の何が悪かったのでしょうか。よろしくご指導ください。

ThisWorkbook　には、別のコードもあったので、少しいじりました。それが悪かったのでしょうか。

ThisWorkbook　の　Workbook　に、下記のコードを記載してあります。

'開いたときに、最初に会員名簿から画面が始まるようにする。
Private Sub Workbook_Open()
Sheets("名簿").Activate
End Sub

'保存するときに
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
'クリア、印刷画面から入力情報を消す（間違って印刷する事故防止のため）
ThisWorkbook.Sheets("印刷").Range("AX16,AQ16,AA16,E15:E17").ClearContents
ThisWorkbook.Sheets("施設").Range("C2").ClearContents
'コピー
ThisWorkbook.Sheets("施設").Range("C3").Value = ThisWorkbook.Sheets("施設").Range("G6").Value
End Sub

'終了するときに
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'クリア 印刷画面から入力情報を消す（間違って印刷する事故防止のため）
ThisWorkbook.Sheets("印刷").Range("AX16,AQ16,AA16,E15:E17").ClearContents
ThisWorkbook.Sheets("施設").Range("C2").ClearContents
'コピー
ThisWorkbook.Sheets("施設").Range("C3").Value = ThisWorkbook.Sheets("施設").Range("G6").Value

'ブックを閉じるとき
Dim Wsh As Worksheet
For Each Wsh In Worksheets(Array("名簿", "施設"))
With Wsh
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
Else
'本来は、ここに、.Range("A1").AutoFilter を加える
End If
End With
Next
ThisWorkbook.Save
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'シートを換えるとき
Select Case Sh.Name
Case "名簿", "施設"
With Sh
If .AutoFilterMode Then
If .FilterMode Then
.ShowAllData
End If
Else
'本来は、ここに、.Range("A1").AutoFilter を加える
End If
End With

End Select
End Sub

回答No.2

Wendy02

こんばんは。

こんな感じでしょうか？
オートフィルタの場所が書かれていないので、オートフィルタがない場合については、省略されています。

コードの意味は、オートフィルタがあるかどうかを調べ、次にフィルターモードになっているかを調べ、フィルターモードなら、全部の項目を出せ、という命令になっています。

以下は、ThisWorkbook に設定してください。

'ThisWorkbook
Private Sub Workbook_BeforeClose(Cancel As Boolean)
'ブックを閉じるとき
　Dim Wsh As Worksheet
　For Each Wsh In Worksheets(Array("印刷", "施設"))
　　With Wsh
　　　If .AutoFilterMode Then
　　　　If .FilterMode Then
　　　　　.ShowAllData
　　　　End If
　　　Else
　　　　　'本来は、ここに、.Range("A1").AutoFilter を加える
　　　End If
　　End With
　Next
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'シートを換えるとき
　Select Case Sh.Name
　　Case "印刷", "施設"
　　　With Sh
　　　　If .AutoFilterMode Then
　　　　　If .FilterMode Then
　　　　　　.ShowAllData
　　　　　End If
　　　　Else
　　　　　　 '本来は、ここに、.Range("A1").AutoFilter を加える
　　　　End If
　　　End With

　End Select
End Sub

お礼

ThisWorkbook に設定する方法を教えていただき、ありがとうございました。

勉強させていただきます。

回答No.1

tinu 2000（@tinu2000）

オートフィルタが設定してある３つのシートに以下のソースを貼り付けて下さい。

Private Sub Worksheet_Activate()
　Range("A1").Select
　Selection.AutoFilter
　Selection.AutoFilter
End Sub

これは、一旦オートフィルタを解除して、再度オートフィルタをかけています。

> 同じく、上記のことをブックを閉じるときにも実行したいのです。
これは、行なう必要がなくなります。
フィルターが掛かった状態で閉じても、ブックを開いてそのシートを表示させると、全件表示されます。

外していたらゴメンです。
ではでは。

お礼

できました。なるほど、一旦オートフィルタを解除して、再度オートフィルタをかけるという考え方をするのですね。

勉強になりました。

ありがとうございました。