こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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


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

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


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

よろしくお願いします。

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

投稿日時 - 2007-01-26 20:10:03

QNo.2698463

困ってます

質問者が選んだベストアンサー

こんにちは。#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 の判定のオプションをつけてみるか、どうかですね。

投稿日時 - 2007-01-28 18:00:13

お礼

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

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

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


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

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

投稿日時 - 2007-01-29 15:36:13

ANo.4

このQ&Aは役に立ちましたか?

1人が「このQ&Aが役に立った」と投票しています

回答(4)

ANo.3

#2 の訂正です。

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

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

投稿日時 - 2007-01-26 23:05:24

補足

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

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

投稿日時 - 2007-01-28 16:11:55

ANo.2

こんばんは。

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

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

以下は、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

投稿日時 - 2007-01-26 22:11:17

お礼

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

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

投稿日時 - 2007-01-28 15:57:25

ANo.1

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

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

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


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


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

投稿日時 - 2007-01-26 21:23:01

お礼

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

勉強になりました。

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

投稿日時 - 2007-01-28 14:23:42

あなたにオススメの質問