VBAのSortで並び替える方法|マクロ記録のままでは失格な理由と1行で書く標準の書き方

Excelシート内のデータを並べ替える「SORT」機能。プログラミングどうしてますか?

みなさん、こんにちは。セルネッツ竹本です。

Excel実務で、データを特定の法則で並べ替えるシーンは、多いもの。
今回のテーマは、「SORT」機能です。
が、記述方法には、大きく2種類に分かれます。

マクロ記録で作った並び替えのコードが長くて、どこを直せばよいかわからない。
そんな経験はありませんか?

結論から言うと、VBAでの並び替え(ソート)は、Range.Sortメソッドを使えば1行で書けます。
マクロ記録のままのSortオブジェクトは行数が多く、後から読む人の負担になります。

この記事では、次のことがわかります。

• Range.Sortの書き方と、昇順・降順・複数条件の指定
• マクロ記録のコードとの違いと、セルネッツが標準にしている書き方
• 並び替えでよくあるエラーと注意点

VBAで並び替える基本:Range.Sortの書き方

VBAでセル範囲を並び替えるときは、Range.Sortメソッドを使います。
並べ替えたい表のセルと、キーになる列、昇順か降順かを指定するだけです。

構文と主な引数

基本の形は、次の1行です。

シート.Range("A1").Sort Key1:=シート.Range("I1"), Order1:=xlDescending, Header:=xlYes

引数	指定する内容	主な値
Key1〜Key3	並べ替えのキーにする列(のセル)	シート.Range(“I1”) など
Order1〜Order3	それぞれのキーの並び順	xlAscending(昇順)、xlDescending(降順)
Header	1行目が見出しかどうか	xlYes(見出しあり)、xlNo(見出しなし。既定値)

並べ替える範囲に1つのセルを指定すると、そのセルを含む表全体が対象になります。
見出しがある表では、Header:=xlYes を必ず付けます。
付けないと、見出しの行までデータとして並べ替えられることがあります。
(参考:Microsoft Learn「Range.Sort メソッド (Excel)」)

昇順・降順と複数条件の指定

複数の条件で並べ替えるときは、Key1・Key2・Key3の順に優先されます。
たとえば「顧客CDの昇順、同じ顧客の中では売上金額の降順」なら、次のように書きます。

O_11.Range("A1").Sort Key1:=O_11.Range("B1"), Order1:=xlAscending, Key2:=O_11.Range("D1"), Order2:=xlDescending, Header:=xlYes

Range.Sortで指定できるキーは3つまでです。
4つ以上の条件が必要なときは、優先順位の低いキーから順に、2回に分けて並べ替えます。

コード中の「O_11」は、シートに付けたオブジェクト名(コード名)です。
シート名が変わってもコードが動き続けるため、弊社では標準にしています。
詳しくはワークシートをID管理すると可読性がUPするプロのテクニックをご覧ください。

マクロ記録のコードとRange.Sortの違い

マクロ記録で並び替えを記録すると、Range.SortではなくSortオブジェクトのコードが作られます。
同じ並び替えでも、行数と読みやすさに大きな差が出ます。

◆Excelバージョンによってマクロ記録した記述が違う

掲載の図、(上)Macro1と(下)Macro2では、どちらが読みやすいと感じますか?

(上)並び替えをマクロ記録した結果を貼り付け

(下)旧バージョン記述をさらにシンプル化

2つの書き方を比べると、次のとおりです。

比べること	Sortオブジェクト(マクロ記録)	Range.Sort(セルネッツの標準)
行数	多い(画像の例では14行)	1行
キーの数	4つ以上も指定できる	3つまで
セルの色・フォントの色での並び替え	できる	できない
読みやすさ・直しやすさ	設定が多く、どこを直せばよいか探しにくい	キー・並び順・見出しが1行で読める

値で並べ替えるだけなら、Range.Sortで足ります。
色で並べ替える場合や、キーが4つ以上ある場合だけSortオブジェクトを使う、という使い分けがおすすめです。

(ソース記述のサンプル)

'[呼び方] Call M_SORT処理("O_11")

'※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※
Sub M_SORT処理(S_St区分 As String)
'※※※※※※※※※※※※※※※※※※※※※※※※※※※※※※

'Header:=xlYes 見出しは1行データで構成される前提で、
'DB移行しやすい状態に(セル結合はNG)
'概念要素は4つ　どのシート(O_11)、どの列(A)、並べ順(昇順)、見出しの有無

Select Case S_St区分

    Case "O_11" '[B列:顧客CD(昇順)] [D列:売上金額(降順)]

        O_11.Range("A1").Sort Key1:=O_11.Range("B1"), Order1:=xlAscending, Key2:=O_11.Range("D1"), Order2:=xlDescending, Header:=xlYes


    Case "O_51" '[A列:会員CD(昇順)] [C列:受注日(降順)]

        O_51.Range("A1").Sort Key1:=O_51.Range("A1"), Order1:=xlAscending, Key2:=O_51.Range("C1"), Order2:=xlDescending, Header:=xlYes

End Select

End Sub

Keyにもシート(O_11)を付けているのがポイントです。
シートを付けずに Range(“B1”) と書くと、別のシートを開いた状態で実行したときにエラーになります。

◆大事なポイントは、3つ!

1. どの列を並び替えのキーにしているか?
2. 昇順か降順か?
3. 見出しはあるか、ないか?

画像のサンプルではシンプルにKeyはたった一つしか指定していませんが、記述の違いはあきらかです。

◆可読性(読みやすさ)が何より大事

プログラミングでは、改修といって仕様を変更するシーンがあります。
作った人ならまだしも、第三者が書いたプログラムを読み解くのはけっこうな労力がいるものです。

Excelはバージョンが2003から2007へ変わったとき、大きな機能拡張がありました。
利用できる色数もワークシートの行数や列数も進化を遂げました。

SORTをマクロ記録した結果も変わってしまいましたが、こればかりは、旧バージョンがシンプルですので、セルネッツでは、こちらを標準化ガイドラインで使用するよう定めています。

読みやすさを高める書き方は、タブインデントで読みやすさアップでも紹介しています。
罫線の描画も、マクロ記録結果のソースを乱用しない書き方が同じ考え方です。

◆メンテナンス性の向上には、Excel標準機能を併用するスキルが大事

システム開発会社で経験を積んだベテランSEとなると、もはやSORTはメモリ上の世界で片付けることも。
せっかく標準機能が実装されているのに、「Quick Sort」など技術で対応するのは、弊社では推奨していません。

理由は、ひとつ。Excelの機能で十分だから。
これは改修の際のコストにも影響するため、低料金サービス提供のリスクとなってしまうのです。

ExcelVBAは他の開発言語に比べ、劣っているところも多分にありますが、Excelブックに対するデータ処理なら、圧倒的に優位性があります。

並び替えでよくあるエラーと注意点

並び替えのトラブルの多くは、範囲と見出しの指定で起きます。

• Keyにシートを付け忘れる:並べ替えるシートとKeyのシートが食い違い、実行時エラー1004になります。KeyにもO_11.Range(“B1”)のようにシートを付けます。
• 見出しの指定を省く:Headerを省くと見出しなし(xlNo)として扱われ、見出しが表の途中に紛れ込むことがあります。
• セル結合がある:大きさの違う結合セルがあると、エラーになって並べ替えられません。見出しは1行にし、結合を使わない表にします。
• 空白の行・列で表が分かれている:1つのセルを指定した場合、空白の行や列の手前までしか並べ替えられません。
• オートフィルターで絞り込んだまま:マクロ記録すると AutoFilter.Sort のコードになり、絞り込みの状態に左右されます。

オートフィルターで絞り込んだ表をVBAで扱うときの落とし穴は、VBAでオートフィルターを解除する方法|絞り込み中に最終行がずれる原因と対策で解説しています。

よくある質問

4つ以上の列で並べ替えたいときは、どうすればよいですか?

Range.Sortを2回に分けて実行します。
先に優先順位の低いキーで並べ替え、次に優先順位の高いキーで並べ替えると、全体が正しい順になります。
Sortオブジェクトを使えば、1回で4つ以上のキーを指定することもできます。

VBAの配列を並べ替えることはできますか?

配列そのものにはSortメソッドがありません。
作業用のシートに書き出してRange.Sortで並べ替え、配列に読み戻す方法が手軽で確実です。
項目ごとにまとめて集計したい場合は、連想配列でグループ集計を効率化も参考になります。

データの行数が毎回変わる場合、範囲はどう指定しますか?

表の左上のセルを1つだけ指定すれば、データの行数が変わっても表全体が並べ替えの対象になります。
ただし、表の途中に空白の行があると、そこで範囲が切れる点に注意してください。

まとめ|並び替えはRange.Sortの1行で書く

VBAでの並び替えは、Range.Sortを使えばキー・並び順・見出しの有無を1行で書けます。
マクロ記録のコードは動きますが、そのまま使うと後から読む人の負担になります。

Keyにもシートを付けること、見出しがあればHeader:=xlYesを付けることの2点を守れば、よくあるエラーも防げます。

---