表から特定の行にだけ処理をしたいことがある。例えば、バスケットボールチームの選手リストがあって、ポジションがPGの選手の行にだけ色を塗りたいといった場合だ。
とりあえず、Wリーグの公式サイトに載っているシャンソンVマジックの選手リストから作成した表を例にしてコードを書いてみる。
'----------------------------------------
' PGの行に色を塗る
'----------------------------------------
Sub ChansonAutoFilterTest01()
Dim dataTable As Range
' 表の範囲
Set dataTable = Sheet2.Range("A1").CurrentRegion
' ポジション列にフィルターをかける
dataTable.AutoFilter Field:=3, Criteria1:="PG"
' フィルターをかけた行に色を塗る(項目行は除く)
With dataTable
.Offset(1, 0).Resize(.Rows.Count - 1).Interior.Color = RGB(0, 255, 0)
End With
' フィルター解除
dataTable.AutoFilter
End Subオートフィルターを使って書いたが、オートフィルターを使わずにループ処理で書くこともできる。どちらがいいのだろうか？
ループ処理で書けば、AutoFilterメソッドについて勉強しなくて済む。しかし、処理速度はループよりオートフィルターの方が速い。だから、オートフィルターを使った方がいいと思う。
もちろん、今回のように17行しかない表ならどちらでも差はないが、これは説明用のサンプルコードである。
次に、PGの選手の名前だけ取得するコードを書いてみる。
'----------------------------------------
' PGの名前のみ取得
'----------------------------------------
Sub ChansonAutoFilterTest02()
Dim dataTable As Range
Dim filteredRow As Range
Dim nameItem As Variant
Dim nameList As Collection
Set nameList = New Collection
' 表の範囲
Set dataTable = Sheet2.Range("A1").CurrentRegion
' ポジション列にフィルターをかける
dataTable.AutoFilter Field:=3, Criteria1:="PG"
' フィルター後の名前列のデータをリストに追加
For Each filteredRow In dataTable.SpecialCells(xlCellTypeVisible).Rows
nameList.Add filteredRow.Cells(1, 2).Value
Next
' 項目名を削除
nameList.Remove 1
' フィルター解除
dataTable.AutoFilter
' 名前を表示
For Each nameItem In nameList
Debug.Print nameItem
Next
End Subこちらは少し面倒な処理が必要になる。フィルターをかけた後、そのまま名前列のみを取得しようとすると、すべてのデータを取得してしまう。だから、 SpecialCells(xlCellTypeVisible).Rowsを使って1行ずつ取り出さなければならない。
取り出したデータはコレクションに追加していくと便利だ。1行目の項目名も取り出してしまうので、nameList.Remove 1で項目名を削除している。追加や削除が簡単にできるからコレクションは便利なのだ。
最後に、PGの平均身長を求めるコードを書いてみる。
'----------------------------------------
' PGの平均身長を求める
'----------------------------------------
Sub ChansonAutoFilterTest03()
Dim dataTable As Range
Dim filteredRow As Range
Dim heightItem As Variant
Dim heightSum As Long
Dim heightList As Collection
Set heightList = New Collection
' 表の範囲
Set dataTable = Sheet2.Range("A1").CurrentRegion
' ポジション列にフィルターをかける
dataTable.AutoFilter Field:=3, Criteria1:="PG"
' フィルター後の身長列のデータをリストに追加
For Each filteredRow In dataTable.SpecialCells(xlCellTypeVisible).Rows
heightList.Add filteredRow.Cells(1, 4).Value
Next
' 項目名を削除
heightList.Remove 1
' フィルター解除
dataTable.AutoFilter
' 身長の合計を求める
For Each heightItem In heightList
heightSum = heightSum + heightItem
Next
Debug.Print "平均身長 : " & (heightSum / heightList.Count)
End Subやっていることは名前を取得するコードとほぼ同じだ。コレクションの扱い方の復習にちょうどいいと思って作ってみた。しかし、平均身長を求めるだけなら、オートフィルターを使うより簡単な方法がある。ワークシート関数のSumIfとCountIfを使う方法だ。
'-------------------------------------------
' PGの平均身長を求める(SumIf,CountIfを使う)
'-------------------------------------------
Sub ChansonSumIfTest01()
Dim dataTable As Range
Dim heightSum As Long
Dim dataCnt As Long
' 表の範囲
Set dataTable = Sheet2.Range("A1").CurrentRegion
' 合計とデータの個数を求める
heightSum = WorksheetFunction.SumIf(dataTable.Columns(3), "PG", dataTable.Columns(4))
dataCnt = WorksheetFunction.CountIf(dataTable.Columns(3), "PG")
Debug.Print "平均身長 : " & (heightSum / dataCnt)
End Sub明らかにこちらの方がわかりやすいし、バグが入る余地も少ないだろう。
VBAは、このようにいろいろな書き方があるからややこしい。ちなみに、いずれのコードでも使っているSheet2というのはシートをシートコードで指定した書き方だ。
どの方法を選択するかで大いに迷う、これはVBAを使うにあたって避けては通れない道なのだ。
