Level5 VBA|クエスト11

『対象』のデータだけを絞り込み、指定した順番へ並べ替えてから転記できないの?
実務では、表にあるすべてのデータではなく、条件に一致したデータだけを並べ替えて転記したい場合があります。
VBAでは、AutoFilterを使って条件に一致するデータを絞り込めます。
対象シート.Range("A1:D" & 最終行).AutoFilter _
Field:=3, _
Criteria1:="対象"
さらに、Sortを使うと、表を昇順や降順へ並べ替えられます。
フィルター後に表示されている行をFor Nextで確認すれば、絞り込んだデータだけを転記先へ上から詰めて転記することも可能です。
この記事では、オートフィルターと並べ替えを実行し、表示されている行だけを別のワークシートへ転記する方法を解説します。
対象列・抽出条件・並べ替え順・処理範囲を変更し、表示されたデータだけを転記できるようになりましょう。
AutoFilterでデータを絞り込む
オートフィルターは、指定した条件に一致するデータだけを表示する機能です。
VBAでは、AutoFilterメソッドを使用します。
Range("A1:D10").AutoFilter _
Field:=3, _
Criteria1:="対象"
このコードでは、A1セルからD10セルまでの表にフィルターを設定し、左から3列目に「対象」と入力されている行だけを表示します。
Range("A1:D10"):フィルターを設定する表全体AutoFilter:オートフィルターを設定するField:=3:表の左から3列目を対象にするCriteria1:="対象":「対象」と等しいデータを表示する
オートフィルタについて確認したい方はこちらをご覧ください。
フィルター範囲にはデータのみでなく、見出し行も含めましょう。
最終行までをフィルター範囲にする
データ件数が変化する表では、最終行を取得して処理範囲へ組み込みます。
Dim 最終行 As Long
最終行 = Range("A" & Rows.Count).End(xlUp).Row
Range("A1:D" & 最終行).AutoFilter _
Field:=3, _
Criteria1:="対象"
最終行取得コードの仕組みや基準列の選び方は、「Excel VBAのFor Nextの使い方|最終行まで繰り返し処理する方法」で詳しく解説しています。
Fieldで対象列を指定する
Fieldは、フィルター範囲のどの列を条件判定に使うか指定する引数です。
Range("A1:D" & 最終行).AutoFilter _
Field:=3, _
Criteria1:="対象"
フィルター範囲がA列からD列の場合、Fieldは次の列に対応します。
Field | 対象列 |
|---|---|
1 | A列 |
2 | B列 |
3 | C列 |
4 | D列 |
ただし、Fieldはワークシート全体の列番号ではありません。
次のようにフィルター範囲がB列からE列の場合、Field:=3が表すのはD列です。
Range("B1:E" & 最終行).AutoFilter _
Field:=3, _
Criteria1:="対象"
Fieldには、フィルター範囲の左端から数えた列番号を指定します。
Criteria1で抽出条件を指定する
Criteria1には、表示したいデータの条件を指定します。
Criteria1:="対象"
「完了」の行を表示する場合は、次のように変更します。
Criteria1:="完了"
数値を比較する条件も指定できます。
Criteria1:=">=100"
この条件では、対象列の値が100以上の行を表示します。
比較演算子と基準値は、1つの文字列としてダブルクォーテーションで囲みます。
比較演算子の使い方を確認したい方はこちらをご覧ください。
複数の条件でデータを絞り込む
実務では、複数の条件を組み合わせてデータを絞り込むことがあります。
- 異なる列へ条件を設定する:すべてを満たす行を表示
Arrayで複数の値を指定する:いずれかに一致する行を表示Criteria1とCriteria2を使う:同じ列に上限と下限を設定
異なる列へ条件を設定する
次のコードでは、2つの条件で表を絞り込みます。
- C列が「対象」
- D列が100以上
対象シート.Range("A1:D" & 最終行).AutoFilter _
Field:=3, _
Criteria1:="対象"
対象シート.Range("A1:D" & 最終行).AutoFilter _
Field:=4, _
Criteria1:=">=100"
同じフィルター範囲に対して、異なるFieldを指定しています。
この場合は、両方の条件を満たす行だけが表示されます。
同じ列から複数の値を表示する
C列が「対象」または「確認中」の行を表示する場合は、Arrayを使用します。
対象シート.Range("A1:D" & 最終行).AutoFilter _
Field:=3, _
Criteria1:=Array("対象", "確認中"), _
Operator:=xlFilterValues
この条件では、「対象」と「確認中」のどちらかに一致する行が表示されます。
同じ列を数値の範囲で絞り込む
D列が100以上200以下の行を表示する場合は、2つの条件を指定します。
対象シート.Range("A1:D" & 最終行).AutoFilter _
Field:=4, _
Criteria1:=">=100", _
Operator:=xlAnd, _
Criteria2:="<=200"
Criteria1:=">=100":100以上Operator:=xlAnd:2つの条件を両方満たすCriteria2:="<=200":200以下
Sortでデータを並べ替える
表を並べ替えるときは、Sortメソッドを使用します。
次のコードでは、B列を基準に昇順で並べ替えます。
対象シート.Range("A1:D" & 最終行).Sort _
Key1:=対象シート.Range("B2"), _
Order1:=xlAscending, _
Header:=xlYes
Range("A1:D" & 最終行):並べ替える表全体Key1:=Range("B2"):並べ替えの基準になるB列Order1:=xlAscending:昇順Header:=xlYes:1行目を見出しとして扱う
並べ替えについて確認したい方はこちらをご覧ください。
昇順と降順を変更する
昇順にはxlAscendingを使用します。
Order1:=xlAscending
降順の場合は、xlDescendingを使用します。
Order1:=xlDescending
数値を並べ替える場合は、昇順なら小さい順、降順なら大きい順です。
複数の列を優先順位付きで並べ替える
複数の列を基準にする場合は、SortFieldsへキーを順番に追加します。
次のコードでは、以下の順番で並べ替えます。
- C列を昇順
- C列が同じデータの中でB列を降順
With 対象シート.Sort
.SortFields.Clear
.SortFields.Add _
Key:=対象シート.Range("C2:C" & 最終行), _
SortOn:=xlSortOnValues, _
Order:=xlAscending
.SortFields.Add _
Key:=対象シート.Range("B2:B" & 最終行), _
SortOn:=xlSortOnValues, _
Order:=xlDescending
.SetRange 対象シート.Range("A1:D" & 最終行)
.Header = xlYes
.Apply
End With
Withで同じオブジェクトの処理をまとめる
Withを使用すると、同じオブジェクトに対する複数の処理をまとめて記述できます。
基本形は、次のとおりです。
With オブジェクト
.プロパティまたはメソッド
.プロパティまたはメソッド
End With
今回のコードでは、次の部分が操作対象です。
With 対象シート.Sort
WithからEnd Withまでの行頭にあるピリオドは、対象シート.Sortに続く処理を表します。
Withを使うことで、同じ対象シート.Sortを何度も書かずに済み、処理のまとまりが分かりやすくなります。
以前の並べ替え条件を消す
.SortFields.Clear
新しい条件を設定する前に、以前の並べ替え条件を消します。
並べ替えのキーを追加する
.SortFields.Add
1つ目に追加したC列が最優先です。
2つ目に追加したB列は、C列の値が同じデータを並べ替えるときに使用されます。
並べ替える表全体を指定する
.SetRange 対象シート.Range("A1:D" & 最終行)
基準列だけでなく、表全体を並べ替えの対象にします。
設定した並べ替えを実行する
.Apply
追加したキーと優先順位で並べ替えを実行します。
SortFields.Addの順番複数キーでは、SortFields.Addを記述した順番が優先順位になります。最優先で並べ替える列を先に追加しましょう。
絞り込んだ行だけを転記する
フィルターを設定すると、条件を満たさない行は非表示になります。
行が表示されているかは、Hiddenプロパティで確認できます。
If 対象シート.Rows(転記元行).Hidden = False Then
この条件は、現在の行が非表示ではない場合、つまり表示されている場合に成立します。
転記元行と転記先行を分ける
表示されている行だけを上から詰めて転記するには、2つの行番号を使用します。
Dim 転記元行 As Long
Dim 転記先行 As Long
| 変数 | 役割 |
|---|---|
転記元行 | フィルターを設定した表を順番に確認する |
転記先行 | 転記先で次に値を入力する行を表す |
転記先のデータが2行目から始まる場合は、最初に次の値を代入します。
転記先行 = 2
表示行だけを値で転記する
For 転記元行 = 2 To 最終行
If 対象シート.Rows(転記元行).Hidden = False Then
転記先シート.Range("A" & 転記先行 & ":D" & 転記先行).Value = _
対象シート.Range("A" & 転記元行 & ":D" & 転記元行).Value
転記先行 = 転記先行 + 1
End If
Next 転記元行
転記先行は、実際に転記した場合だけ1増えます。
そのため、転記元に非表示の行があっても、転記先には空き行ができません。
このコードでは、フィルターで非表示になった行だけでなく、手動で非表示にした行も転記されません。
フィルター・並べ替え・転記の完成例
次のマクロでは、以下の処理を実行します。
- C列が「対象」の行へ絞り込む
- D列が100以上の行へさらに絞り込む
- A列を昇順で並べ替える
- A列の値が同じデータの中で、B列を降順に並べ替える
- 表示された行だけを「抽出結果」シートへ上から詰めて転記する
Sub 対象データを並べ替えて転記する()
Dim 対象シート As Worksheet
Dim 転記先シート As Worksheet
Dim 最終行 As Long
Dim 転記元行 As Long
Dim 転記先行 As Long
Set 対象シート = ThisWorkbook.Worksheets("売上表")
Set 転記先シート = ThisWorkbook.Worksheets("抽出結果")
最終行 = 対象シート.Range("A" & _
対象シート.Rows.Count).End(xlUp).Row
対象シート.Range("A1:D" & 最終行).AutoFilter _
Field:=3, _
Criteria1:="対象"
対象シート.Range("A1:D" & 最終行).AutoFilter _
Field:=4, _
Criteria1:=">=100"
With 対象シート.Sort
.SortFields.Clear
.SortFields.Add _
Key:=対象シート.Range("A2:A" & 最終行), _
SortOn:=xlSortOnValues, _
Order:=xlAscending
.SortFields.Add _
Key:=対象シート.Range("B2:B" & 最終行), _
SortOn:=xlSortOnValues, _
Order:=xlDescending
.SetRange 対象シート.Range("A1:D" & 最終行)
.Header = xlYes
.Apply
End With
転記先行 = 2
For 転記元行 = 2 To 最終行
If 対象シート.Rows(転記元行).Hidden = False Then
転記先シート.Range("A" & 転記先行 & _
":D" & 転記先行).Value = _
対象シート.Range("A" & 転記元行 & _
":D" & 転記元行).Value
転記先行 = 転記先行 + 1
End If
Next 転記元行
If 対象シート.FilterMode Then
対象シート.ShowAllData
End If
End Sub
このマクロは、次の順番で処理します。
- 対象シートと転記先シートを指定する
- A列を基準に最終行を取得する
- C列が「対象」、かつD列が100以上の行へ絞り込む
- A列を昇順で並べ替える
- A列が同じデータの中で、B列を降順に並べ替える
- 表示行だけを転記先へ上から詰めて転記する
- 絞り込みを解除する
転記先に以前のデータが残る場合は、必要に応じて事前にClearContentsで削除する
フィルターを解除する
絞り込みを解除するときは、ShowAllDataを使用します。
対象シート.ShowAllData
絞り込みが行われていない状態で実行するとエラーになる場合があるため、FilterModeで確認します。
If 対象シート.FilterMode Then
対象シート.ShowAllData
End If
フィルターの矢印も非表示にする場合は、次のコードを使用します。
対象シート.AutoFilterMode = False
ShowAllData:絞り込みを解除するAutoFilterMode = False:フィルターの矢印も消す
まとめ|複数条件で絞り込み、表示行だけを転記しよう
オートフィルタと並べ替えを自動化させましょう。
AutoFilterで表を絞り込むFieldはフィルター範囲の左から数えた列番号Criteria1で抽出条件を指定する- 異なる列へ条件を設定すると「かつ」の絞り込みになる
Arrayで同じ列の「または」条件を指定できるSortFields.Addを記述した順番が並べ替えの優先順位になるxlAscendingは昇順、xlDescendingは降順Hidden = Falseなら行が表示されている- 転記元行と転記先行を分けると、上から詰めて転記できる
ShowAllDataで絞り込みを解除できる
次のクエストへ
今回は、複数条件で表を絞り込み、複数の列を優先順位付きで並べ替えてから、表示行だけを転記する方法を学びました。
次のクエストでは、Callを利用して他のマクロを呼び出す方法を学びましょう。
Level5の学習順序を確認したい方は、Level5攻略フローをご覧ください。








コメント