[[20150512192204]] 『オートフィルター自動化のついて』(林檎ひめ) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『オートフィルター自動化のついて』(林檎ひめ)

下記サイトから質問です。
http://www.excel.studio-kazu.jp/kw/20050608211016.html

 Private Sub
  Worksheet_Change(ByVal Target As Range)
   If Target.Address(0, 0) = "D2" Then
    With Range(Range("B7"), Range("B7").End(xlToRight).End(xlDown))
     .AutoFilter Field:=2, Criteria1:=Target.Text
    End With
   If Len(Target) = 0 Then Cells.AutoFilter
  End If
 End Sub

このコードを参照し抽出したいのですが、
D2が関数で表示さてていると抽出されません。
又、ブランクにすると全データ表示されるのは
良いのですがオートフィルター自体が解除されて
しまうため再び抽出できなくなります。

この二点何とかなりませんでしょうか。
アドバイスお願いします。

< 使用 Excel:Excel2007、使用 OS:unknown >


 Changeイベントは式による値の変化では実行されません。
 ただ、ここで参照している別セルの変更では実行されます。
 たとえば D2 が =MATCH(F1,H1:H10,FALSE) なんて式であれば、F1 の変更で発生しますから、
 Targetのチェックを F1 にしてやればいけます。
 (あっ、H1:H10の変更でも発生します。なので、F1か H1:H10の中のいずれかのセルという判定ですね)
 ただし、フィルタリングのもとは、あくまで D2 でしょうから、
 Criteria1:=Target.Text ではなく Criteria1:=Range("D2").Text ですね。

 ここに、具体的にどのような式が入っているのかをアップすると、具体的にアドバイス可能です。

 空白の場合に具合が悪いのであれば、当該セルが空白なら なにもせずに Exit Sub とか。

(β) 2015/05/12(火) 20:06


βさん、ありがとうございます
D2には=If(d1="","",vlookup(d1,meibo'!$a$2:$c$30,3))
の関数が入っています
教えていただいた書き換え明日やってみます!

空白の場合何もしないと全件表示されないのでフィルタは
解除してもオートフィルターの設定が消えないようにしたい
のですがどのコードが解除コードなのかが分かりません。

できればどのコードが何をしているか教えていただけないでしょうか。
よろしくお願いします。
(林檎ひめ) 2015/05/12(火) 22:02


 この式なら、まずは Target.Address チェックを D1 にしてやれば、ほぼ、OKだと思います。
 なぜ、「ほぼ」かというと、D1 の変更以外に meiboシートの A2:C30の内容に変更があった場合も
 D2 は変更されるはずですけど、このシートではないので、そのチェックができません。
 まぁ、ここはデータベースのようなもので、処理中の変更はないのでしょうね?
 (もしあれば、もう一工夫必要です)

 さらに、コメントしたように、 Criteria1:=Target.Text ではなく Criteria1:=Range("D2").Text 。

 で、このコードですけど

    If Target.Address(0, 0) = "D2" Then
       '
       '
       '処理
       '
       '
    End If

 これは、(入力による)変更のあったセル領域(Target) が D2 単一セルの場合にのみ 処理。
 それ以外の変更の場合は何もしません。

        With Range(Range("B7"), Range("B7").End(xlToRight).End(xlDown))
            .AutoFilter Field:=2, Criteria1:=Target.Text
        End With

 この中の Range(Range("B7"), Range("B7").End(xlToRight).End(xlDown))
 ちょっとマニアック(?)な記述ですね。
 オートフィルターの領域を指定しているんですが、
 B7から始まり、B7から右に空白セルになる手前の列で、そこから下に空白セルになる前の行のセルまでを
 領域としています。

 で、

            .AutoFilter Field:=2, Criteria1:=Target.Text

 これは、その中の2列目、つまり C7から下のC列に対して、変更のあったセルの値でフィルタリングしています。

 意図がわからないのが

        If Len(Target) = 0 Then Cells.AutoFilter    '★

 これですね。
 ここでは、フィルタリングをかけた値の桁が 0 なら(つまり空白、あるいは、長さゼロの文字列)の場合は
 オートフィルターを解除。 なぜ、このコードがあるのかはわかりません。

 でも、(林檎ひめ)さんが、「オートフィルター自体が解除されてしまうため再び抽出できなくなります。」
 と、困っているなら、このコードを消せばいいんですが?

 ただ、オートフィルターは解除されますが、また、この WorkSheet_Changeが実行されれば、オートフィルターが
 設定され、抽出はされますよ?(もちろん、変更セルの判定を正しくすればということですけど)

(β) 2015/05/12(火) 23:17


βさん、丁寧に教えていただきありがとうございます
抽出できなくなる原因が項目に空白があった為オートフィルター解除後に
抽出できなくなることがわかりました
無事やりたいことができるようになりました

ありがとうございました
(林檎ひめ) 2015/05/14(木) 12:56


追加質問です(すいません)

       If Len(Target) = 0 Then Cells.AutoFilter    '★

このコード、解除せずに全件表示する場合はなんて書けばいいでしょうか?
よろしくお願いします。
(林檎ひめ) 2015/05/15(金) 13:07


 全件表示は シート.ShowAllData なんですが、オートフィルターで絞り込がされていない状態だと(つまりすでに全件表示されている)
 エラーになっちゃいますので、

    If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData

 こんなように記述してください。

(β) 2015/05/15(金) 13:34


βさんありがとうございます
早速入れてみたんですが、抽出する場合も全件になります。
書き換え方がまずいのでしょうか?
何度もすいませんがよろしくお願いします。
(林檎ひめ) 2015/05/15(金) 14:11

 17:58 コード訂正。

 >>抽出する場合も全件になります。 

 ん? 抽出する場合って何のことですか?

 抽出そのものは .AutoFilter Field:=2, Criteria1:=Target.Text でやってるんですよね。

 で、抽出後、(目的はわかりませんが)Target が空白なら If Len(Target) = 0 Then Cells.AutoFilter     でオートフィルター設定を解除してたんですよね?

 ここを解除じゃなく全件表示にしたいということだったのでは?

 もし、そうじゃなく、シート上で入力された値があればオートフィルター実行、そうじゃなければ全件表示と
 こういうことがしたかった?

 そういうことなら以下(Targetチェックのところは、そちらで変更したものにしてくださいね)

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address(0, 0) = "●●" Then
        If Range("D2").Value= "" Then
            If ActiveSheet.FilterMode Then ActiveSheet.ShowAllData
        Else
            With Range(Range("B7"), Range("B7").End(xlToRight).End(xlDown))
                .AutoFilter Field:=2, Criteria1:=Target.Text
            End With
        End If
    End If
 End Sub

(β) 2015/05/15(金) 14:37



βさんありがとうございます
日本語足らずですいません
教えていただいたコードをかきかえると
D2が空白の場合に、オートフィルター設定が残ったまま
全件表示、データが入っていれば抽出にしたかったんですが
空白の場合上手くオートフィルター設定のまま全件表示され
データが入った時も全件表示される状態になりました
該当行を下記のように書き換えたら上手くいきました
追記でコードを記載いただきましてありがとうございました

親切に教えていただきまして本当にありがとうございました

 Private Sub
  Worksheet_Change(ByVal Target As Range)
   If Target.Address(0, 0) = "F1" Then
    With Range(Range("B7"), Range("B7").End(xlToRight).End(xlDown))
     .AutoFilter Field:=2, Criteria1:=Range("D2").Text
    End With
   If Len(Target) = 0 Then ActiveSheet.ShowAllData →ここです
  End If
 End Sub
(林檎ひめ) 2015/05/16(土) 00:24

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.