VBA｜Sortで空白セルを無視して並び替えする方法

Sortオブジェクトを使っていますか？

Sortするときに空白セルが混ざっていると、並び替えが上手くいかなくて困っていませんか？

今回は空白セルを無視して上手く並び替えるSortの方法について徹底的に説明していきます。

参考：Excel VBA入門 その45：Sortのエラー原因・対処法 (Apply Error’1004’など)

Sortで空白セルを無視して並び替えする方法

空白セル以降で並び替えが行われない

以下のように10行目に空白セルが混ざっている表があります。

この表を「C列の種類」及び「D列の値段」を昇順で並び替えたいとしたときにSortオブジェクトを使います。まず、よくある上手くいかないコード例を掲載します。

Sub 上手くいかない例()
    With ActiveSheet.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("C2"), SortOn:=xlsortonvalue, Order:=xlAscending
        .SortFields.Add Key:=Range("D2"), SortOn:=xlsortonvalue, Order:=xlAscending
        .SetRange Range("A1").CurrentRegion　　'←ここが上手くいかない原因です。
        .Header = xlYes
        .Apply
    End With
End Sub

実行してみると、以下のように空白セルがある10行目以降に対してSortが行われません。

この原因は、コードの６行目の「.SetRange Range(“A1”).CurrentRegion」です。

このコードは、Sortするための範囲を設定するものです。

Range(“A1”).CurrentRegionは、並び替える範囲をセルA1 を含む表全体を取得するために、空白のセルで囲まれた範囲を読み取り、参照するプロパティです。

空白のセルで囲まれた範囲を読み取るため、空白のセルの行で読み取りを終えてしまうのです。

したがって、Sortをしても空白セル行以降の並び替えが行われなくなってしまうのです。

UsedRangeプロパティを使う

これを解決するには、UsedRangeプロパティを使うことが挙げられます。

UsedRangeは使用されているセルの範囲を取得するものです。

データが入っているセルの範囲がすべて取得されるので、空白行があっても問題ありません。

UsedRangeを使用した改善VBAコードは以下のようになります。

Sub UsedRangeを使ってソート()
With ActiveSheet.Sort
.SortFields.Clear
.SortFields.Add Key:=Range(“C2”), SortOn:=xlsortonvalue, Order:=xlAscending
.SortFields.Add Key:=Range(“D2”), SortOn:=xlsortonvalue, Order:=xlAscending
.SetRange ActiveSheet.UsedRange　’←シートに対してUsedRangeを使用する
.Header = xlYes
.Apply
End With
End Sub

実行結果は、以下のようになります。

 

表全体にSortが実行され、空白セルが一番下に並び替えられました。

【まとめ】Sortで空白セルを無視して並び替えする方法

今回は空白セルを無視して並び替える方法について説明しました。

Sortオブジェクトを使用する場合は、 CurrentRegion プロパティを使うのではなく、UsedRangeプロパティを使用することで空白セルを無視して並び替えることができます。

Excelには並び替えは必須ですので、ぜひ今回の記事を役立ててみてくださいね。