VBA19_Tips【オートフィルタ自作中級編】

こんばんは! フクロウ村農協のBenです。前回はセルの入力規則を設定してリスト表示をして、条件に合致するデータのみ表示しました。でも、お気づきですか? 前回のドロップダウンリスト。

20151120b.gif
トマトが3つあったり、カボチャも2つ。つまりリストの中に同じものが複数あるのです。これだと親切でないですよね。VBAを用いないで重複しないリストを作るのには、一時作業用の場所を別に用意するとか工夫が必要です。ではVBAではどうするのでしょうか。これまで復習した簡単な命令だけで作ってみます。

20151122a.gif
まず、前回のB2セルは削除して左に詰めます。ついでに2行目を広げています。次に、開発タブの挿入から、御覧のようなActiveXのコンボボックスをクリックして、所定の位置にドラッグします。作物選択:もセルの配置で垂直方向を上にして配置しましょう。コンボボックスの中身はまだ何もないので、プログラムで入れてやります。今回は手っ取り早く、Sheet1のモジュール(Sheets1タブを右クリックして出るシート)を利用して作ってみました。

20151122c.gif
Sheet1のモジュールが出たら上の図のように左のドロップダウンリストからWorksheet、次に右のドロップダウンリストからActivateを選択します。そうすると、Private Sub Worksheet_Activate()ができますので、ここに書きます。この説明は次回(^^;

Private Sub Worksheet_Activate()
  Dim i As Integer, j As Integer, n As Boolean
  ComboBox1.Clear
  ComboBox1.AddItem ("すべて")
  i = 4
  Do Until IsEmpty(Cells(i, 1))
    n = True
    For j = 0 To ComboBox1.ListCount - 1
      If ComboBox1.List(j) = Cells(i, 2) Then n = False
    Next
    If n = True Then ComboBox1.AddItem (Cells(i, 2).Value)
    i = i + 1
  Loop
End Sub
今回のコードです。ちょっと複雑ですが、ComboBox1というのが先ほどシートに張り付けたものです。3行目のClearでComboBox1のリストを消去します。次に全部表示させるためにすべてをリストに追加。AddItemはリストに加えるというメソッドです。データの開始行は4行目からなのでi=4、空白になるまでループです。

ここからが新しいところですが、ここではnというブール型の変数(データ型についてはこちら)を用いました。はじめTrueにしておいて、If文で登録済みリストに既にあったらFalseにします。j変数を2個目のループとして入れ子にしてリストの数だけループします。全部照合して一致するのがなければ(n=trueのとき)リストに追加します。これを繰り返すと・・・

20151122d.gif
このように表のデータから重複しないリストを作ることができます。ではこのコンボボックスで選択した値でデータの抽出をするにはどうすればよいでしょうか。

Private Sub ComboBox1_Change()
  Dim i As Integer
  i = 4 'データが始まる行番号
  Do Until IsEmpty(Cells(i, 1))
    If Cells(i, 2) = ComboBox1.Value Then
      Rows(i).EntireRow.Hidden = False
    ElseIf ComboBox1.Value = "すべて" Then
      Rows(i).EntireRow.Hidden = False
    Else
      Rows(i).EntireRow.Hidden = True
    End If
    i = i + 1 '次の行へ
  Loop
End Sub
今度も初めのPrivate Subのとおり、エディタ上部の二つ左右に並んでいるドロップダウンリストから左をComboBox1、右をChangeにして、ComboBox1_change()となったところに入力します。これは、ElseIfですべてを選択した場合が増えたのと、抽出対象を以前のRange("B2")からComboBox1に変えた以外はほぼ前回とほぼ同じです。これで実行ボタンを押さなくても選択しただけでデータが抽出されますので、ボタンは削除しました。次回は今回説明を省略したイベントについてです。

この記事へのコメント


この記事へのトラックバック