ユーザーフォームのコンボボックスに表示する検索履歴にフィルターをかけて重複文字を非表示にする

Excel VBA コンボボックス

前回の「ユーザーフォームのコンボボックスに表示する検索履歴の重複を自動的に削除する」記事では、ファイル検索と同時に重複するファイル名を削除して、検索履歴に残らないようにする方法を紹介しました。

今回は、検索履歴のデータを削除するのではなく「オートフィルター」の「重複するレコードは無視する」機能で一時的に見えなくして、コンボボックスの検索履歴に表示したいと思います。

オートフィルターのコードを「マクロの記録」で作成する

エクセルのリボンの「並べ替えとフィルター」グループ内の「詳細設定」ボタンを押したときの一連の動作をマクロの記録をして、作成したコードを「UserForm1」のプログラムに組み込みます。

1. 9月のブログで作成した「ユーザーフォームのコンボボックスに検索の履歴を表示する.xlsm」のエクセルファイルを開きます。

2.「開発」タブ →「マクロの記録」で記録を開始します。

3. フィルターを設定したい表内のセルを一か所選択する。ここではセルA1を選択しました。

4.「データ」タブの「並べ替えとフィルター」グループ内の「詳細設定」をクリックします。

5. 「フィルターオプションの設定」のダイアログボックスが表示されるので「重複するレコードは無視する」に☑チェックを入れ「OK」ボタンを押します。

6. 「記録終了」→「VIsual Basic」を選択して

7. VBAProjectの標準モジュールのModule2をダブルクリックしてコードウインドウを開く。

標準モジュールに記録されたコードは次のようになりました。

Sub Macro2()
'
' Macro2 Macro
'

'
Range("A1").Select
Range("A1:A6").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
End Sub

A列全体に範囲を広げて処理したいので、「Range(“A1:A6”)」を「ActiveSheet.Columns(“A”)」にコードを書き換えてから「ActiveSheet.Columns(“A”).AdvancedFilter Action:=xlFilterInPlace, Unique:=True」のコードをコピーします。

Sub Macro2()
'
' Macro2 Macro
'

'
Range("A1").Select
ActiveSheet.Columns("A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
End Sub

オートフィルターのコードの貼り付け

VBAProjectの「UserForm1」を選択して、右上の「コードの表示」ボタンをクリックするとコードウインドウを開きます。

先ほどコピーした「ActiveSheet.Columns(“A”).AdvancedFilter Action:=xlFilterInPlace, Unique:=True」のコードを「UserForm1」のプログラムの上から11行目に貼り付けます。

Private Sub UserForm_Initialize()              'ユーザーフォームが表示されるタイミングでフォームの内容を準備する。

Dim i As Long

For i = 2 To Cells(Rows.Count, "A").End(xlUp).Row 'A列の2行目からデータがある最後の行まで処理を繰り返す。

ComboBox1.AddItem Cells(i, "A") 'A列のセルの値をコンボボックスに入力します。

Next i

End Sub

Private Sub CommandButton1_Click()

With ThisWorkbook.Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp) 'セルにデータがある最終行を取得します。

.Offset(1, 0).Value = ComboBox1.Text 'コンボボックスに入力した値をA列の検索履歴に書き込みます。

End With

ActiveSheet.Columns("A").AdvancedFilter Action:=xlFilterInPlace, Unique:=True 'フィルターで一時的に見えなくする。

Dim name As String
name = UserForm1.ComboBox1.Text

Dim ブック As Workbook
Dim Flag As Boolean

For Each ブック In Workbooks '検索したファイルが開いているか調べます。

If ブック.name = name & ".xlsx" Then

Flag = True

End If

Next ブック

If Flag = True Then '検索したファイルが開いていた場合の処理。

MsgBox "「" & name & "」" & "のファイルは既に開いているので最前面に表示します。"

Workbooks(name & ".xlsx").Activate '開いていたファイルを最前面に表示します。

Unload UserForm1 'ユーザーフォームを閉じます。

Exit Sub

Else '検索したファイルが開いていない場合はファイルを開きます。

If Dir(ThisWorkbook.Path & "\フォルダ\" & name & ".xlsx") <> "" Then 'ファイルがフォルダ内にあるか調べます。

Workbooks.Open Filename:=ThisWorkbook.Path & "\フォルダ\" & name & ".xlsx" 'ファイルを開きます。

Unload UserForm1 'ユーザーフォームを閉じます。

Else 'ファイルがフォルダ内に無いときはメッセージを表示します。

Unload UserForm1 'ユーザーフォームを閉じます。

MsgBox "ファイルはフォルダ内にありません。"

UserForm1.Show vbModeless 'ユーザーフォームをモードレスで表示します。

End If

End If

End Sub

これでファイル検索と同時にオートフィルターが有効になり、一時的に重複する履歴を見えなくして、コンボボックスに検索履歴が表示できるように設定できました。

エクセルの検索機能は、フィルターによって非表示になっている行を自動的にスキップする仕様になっているので、重複しない履歴だけが検索されてコンボボックスに表示されます。

オートフィルターを解除する

セルA1を選択しておいて「データ」タブの「フィルター」ボタンを押すとオートフィルターを解除することができます。