Excel VBAでフィルターと並べ替えを行う方法|条件や範囲の変更も解説

Level5 VBA・マクロ

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対象列
1A列
2B列
3C列
4D列

ただし、Fieldはワークシート全体の列番号ではありません。

次のようにフィルター範囲がB列からE列の場合、Field:=3が表すのはD列です。

Range("B1:E" & 最終行).AutoFilter _
    Field:=3, _
    Criteria1:="対象"
Fieldの数え方

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へキーを順番に追加します。

次のコードでは、以下の順番で並べ替えます。

  1. C列を昇順
  2. 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

このマクロは、次の順番で処理します。

  1. 対象シートと転記先シートを指定する
  2. A列を基準に最終行を取得する
  3. C列が「対象」、かつD列が100以上の行へ絞り込む
  4. A列を昇順で並べ替える
  5. A列が同じデータの中で、B列を降順に並べ替える
  6. 表示行だけを転記先へ上から詰めて転記する
  7. 絞り込みを解除する
転記先のデータにも注意

転記先に以前のデータが残る場合は、必要に応じて事前に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攻略フローをご覧ください。

コメント

タイトルとURLをコピーしました