【VBA】オートフィルターを使う

VBAでオートフィルターを扱う方法をご紹介します。

VBAでオートフィルターを設定する
オートフィルターを解除する
参考

VBAでオートフィルターを設定する

RangeのAutoFilterを使用

VBAでオートフィルターを使用するにはRangeのAutoFilterメソッドを使用します。

FilterTestシートにデータが入力されています。

このシートから下記の条件のデータをオートフィルターを使用して表示してみます。

種類が野菜
名前が空白ではない
備考が空白である

オートフィルターを設定するコード

オートフィルターを設定するコードは以下になります。

Option Explicit
Sub setFilter(sht As Worksheet)
'###################################################################################
'シートにオートフィルターを設定する
'先頭行をヘッダーとする
'-----------------------------------------------------------------------------------
'引数  :ws       シートオブジェクト
'###################################################################################
  
  Dim lastRow As Long
  Dim lastCol As Long
  
  lastRow = getMaxRowUsedRange(sht)
  lastCol = getMaxColUsedRange(sht)
  
 
  sht.Range(sht.Cells(1, 1), sht.Cells(lastRow, lastCol)).AutoFilter _
                             field:=1, Criteria1:="野菜"  '入力されている値が「野菜」
  sht.Range(sht.Cells(1, 1), sht.Cells(lastRow, lastCol)).AutoFilter _
                             field:=2, Criteria1:="<>"    '空白ではない
  sht.Range(sht.Cells(1, 1), sht.Cells(lastRow, lastCol)).AutoFilter _
                             field:=3, Criteria1:="="     '空白である
  
End Sub

Function getMaxRowUsedRange(sht As Worksheet) As Long
  
  getMaxRowUsedRange = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row
  
End Function
Function getMaxColUsedRange(sht As Worksheet) As Long
  
  getMaxColUsedRange = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column
  
End Function

Option Explicit

Sub setFilter(sht As Worksheet)

'###################################################################################

'シートにオートフィルターを設定する

'先頭行をヘッダーとする

'-----------------------------------------------------------------------------------

'引数 :ws シートオブジェクト

'###################################################################################

Dim lastRow As Long

Dim lastCol As Long

lastRow = getMaxRowUsedRange(sht)

lastCol = getMaxColUsedRange(sht)

sht.Range(sht.Cells(1, 1), sht.Cells(lastRow, lastCol)).AutoFilter _

field:=1, Criteria1:="野菜" '入力されている値が「野菜」

sht.Range(sht.Cells(1, 1), sht.Cells(lastRow, lastCol)).AutoFilter _

field:=2, Criteria1:="<>" '空白ではない

sht.Range(sht.Cells(1, 1), sht.Cells(lastRow, lastCol)).AutoFilter _

field:=3, Criteria1:="=" '空白である

End Sub

Function getMaxRowUsedRange(sht As Worksheet) As Long

getMaxRowUsedRange = sht.UsedRange.Rows(sht.UsedRange.Rows.Count).Row

End Function

Function getMaxColUsedRange(sht As Worksheet) As Long

getMaxColUsedRange = sht.UsedRange.Columns(sht.UsedRange.Columns.Count).Column

End Function

  sht.Range(sht.Cells(1, 1), sht.Cells(lastRow, lastCol)).AutoFilter _
                             field:=1, Criteria1:="野菜"  '入力されている値が「野菜」

sht.Range(sht.Cells(1, 1), sht.Cells(lastRow, lastCol)).AutoFilter _

field:=1, Criteria1:="野菜" '入力されている値が「野菜」

field引数には数値を設定します。
Rangeで指定した範囲内の何列目のデータに対して
オートフィルターの条件を設定するか指定します。
Rangeで指定した範囲の最も左の列が1となります。

今回はAからC列までの範囲を指定するので、A列が1,B列が2、C列が3になります。

Criteria1にはオートフィルターの条件を指定します。

“文字列”	文字列と同じものだけを表示
“<>”	空白ではないものを表示
“=”	空白であるものを表示

シートの最終行と最終列を取得するために
getMaxRowUsedRangeとgetMaxColUsedRangeを使用しています。

【VBA】UsedRangeで最終行・最終列を取得する

UsedRangeを使用して、シート上の最終行と最終列を取得するプロシージャを作成してみます。 Sheet1に下記のようにデータが入力されています。 D列に最終行、5行目に最終列が入力されています。 UsedRan...

オートフィルター設定コードのテスト

test_setFilterを実行するとオートフィルターを設定します。

Option Explicit
Sub test_setFilter()

  Dim sht As Worksheet
  Set sht = ThisWorkbook.Worksheets("FilterTest")
  Call setFilter(sht)
  
  Set sht = Nothing
  
End Sub

Option Explicit

Sub test_setFilter()

Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets("FilterTest")

Call setFilter(sht)

Set sht = Nothing

End Sub

実行結果は以下になります。
3行目のデータのみ条件に合っているので、3行目のデータだけ表示されます。
他のデータは条件が違うのでフィルターが掛かり、表示されません。

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

オートフィルターを解除するにはWorksheetオブジェクトのFilterModeを変更します。

オートフィルターを解除するコードは以下になります。

Option Explicit
Sub clearFilter(sht As Worksheet)
'###################################################################################
'シートにオートフィルターが設定されている場合、解除する
'-----------------------------------------------------------------------------------
'引数  :ws       シートオブジェクト
'###################################################################################

  If sht.FilterMode Then sht.ShowAllData

End Sub
Sub test_clearFilter()

  Dim sht As Worksheet
  Set sht = ThisWorkbook.Worksheets("FilterTest")
  If sht.FilterMode Then sht.ShowAllData
  
  Set sht = Nothing
  
End Sub

Option Explicit

Sub clearFilter(sht As Worksheet)

'###################################################################################

'シートにオートフィルターが設定されている場合、解除する

'-----------------------------------------------------------------------------------

'引数 :ws シートオブジェクト

'###################################################################################

If sht.FilterMode Then sht.ShowAllData

End Sub

Sub test_clearFilter()

Dim sht As Worksheet

Set sht = ThisWorkbook.Worksheets("FilterTest")

If sht.FilterMode Then sht.ShowAllData

Set sht = Nothing

End Sub

  If sht.FilterMode Then sht.ShowAllData

9	If sht.FilterMode Then sht.ShowAllData

FilterModeがTrueのときはシートにオートフィルターが掛かっているので
WorksheetオブジェクトのShowAllDataメソッドを使用して
データをすべて表示します。

参考

Range.AutoFilter メソッド (Excel)

Range.AutoFilter メソッド (Excel)

Office VBA リファレンストピック

Worksheet.ShowAllData メソッド (Excel)

Worksheet.ShowAllData メソッド (Excel)

Office VBA リファレンストピック