並び替えVBA【作業効率をアップさせる並び替えの方法を紹介】

豆父ちゃん

とても便利な並び替えという処理をVBAで自動化したい。

こんな思いにお答えします。

結論からいうと、VBAでSortメソッドを使うと簡単に並び替えの処理を自動化することができます。

Sortメソッドの基本的な使い方とSortメソッドが使われているサンプルVBAを紹介します。

1 並び替え【Sortメソット】の基本的な使い方
- 1.1 Sortメソットの基本構造
- 1.2 並び替えの範囲を指定
2 並び替え【Sortメソット】を使ったサンプルVBA
3 仕事でも使える並び替え(Sortメソット)VBA
4 並び替えVBA(ユーザー設定の基本な使い方)
- 4.1 ユーザー定義を使った並び替えVBAの作り方
- 4.2 SortFieldsの基本構造
5 並び替えVBAは作業の効率化に欠かせない機能

並び替え【Sortメソット】の基本的な使い方

Sortメソットの基本構造

Range( ).Sort（Key1 , Order1 , Key2 , Type, Order2 , Key3 , Header , OrderCustom , MatchCase , Orientation , SortMethod , DateOption1 , DateOption2 , DateOption3）

Key	省略可能	並び替えのキーになる列のセルを指定
Order	省略可能	キーの列を昇順か降順を指定 xlAscending⇒昇順 XlDescending⇒降順
Type	省略可能	ピボットテーブルを並び替えるときに指定
Header	省略可能	先頭行をタイトル行とするかを指定 xlGuess⇒タイトル行を自動指定 xlNo⇒タイトル行無 xlYes⇒タイトル行有
OrderCustom	省略可能	並び替えの順序を指定
MatchCase	省略可能	大文字と小文字を区別するかを指定 True⇒区別する False⇒区別しない
Orientation	省略可能	並び替えの方向を指定 xlSortColumns⇒上から下へ xlSortRows⇒左から右へ
SortMethod	省略可能	ふりがなを使うか指定 xlPinYin⇒ふりがなを使う xlStroke⇒ふりがなを使わない
DateOption	省略可能	数値と文字列を別にして並び替えるかを指定 xlSortNormal⇒数値と文字列を別に並び替える xlSortTextAsNumbers⇒数値と文字列を同様に並び替える

並び替えの範囲を指定

並び替えVBAでは範囲を指定することはとても重要なのでしっかりと覚えましょう。いくつかのサンプルを見比べるとわかりやすいのでサンプルを3つ紹介します。

サンプル1

1	Range( “A1:C5" ) . Sort ( Key1・・・

⇒ セルA1からC5までを範囲指定

サンプル2

1	Range( Cells ( 1 , 1 ) , Cells ( 5 , 3 )) .Sort( Key1・・・

⇒ セルA1からC5までを範囲指定

サンプル3

1	retu = Cells ( 1, Columns.Count ) . End( xlToLeft ) .Column
2	gyo = Cells ( Rows.Count , 1) .End( xlUp ) .Row
3	Range( Cells ( 1 , 1 ) , Cells ( gyo , retu )) .Sort( Key1・・・

⇒ セルA1から表の右下C5までを範囲指定

範囲を指定することはとても重要で、エクセル業務によってSheet全体が範囲対象になるとは限りません。こっちの表は日付で並び替え、あっちの表は価格で並び替えてくれ！といったことも

サンプル3のVBAを使えば表の行や列の増減を自動的に変更することも可能です。

並び替え【Sortメソット】を使ったサンプルVBA

A1セルからC20の表で、C列を昇順に並び替えを行う。一番上の行はタイトルとして使用する！というVBAを作ってみます。

サンプルVBA

1	Sub テスト()
2	Range(Cells(1, 1), Cells(20, 3)).Sort Key1:=Cells(1, 3), Order1:=xlAscending, Header:=xlYes
3	End Sub

実行結果

簡単なものだと、たったの1行で並び替えの自動化が可能です。

仕事でも使える並び替え(Sortメソット)VBA

電気用品に関する適当に作った一覧表を使って4種類の並び替えVBAを紹介します。

設備番号を昇順で並び替え
設備番号にある枝番(1-1や6-1)を認識して並び替え
ユーザー定義設定を使って地域の都道府県を上から順番に並び替え(ユーザー定義の順番はプログラムに直接記載)
ユーザー定義設定を使って地域の都道府県を下から順番に並び替え(ユーザー定義の順番は設定シートの順番を取得)

この4種類の並び替えVBAを扱えるようになると、いろんな業務に応用することが可能になります。私の職場ではよく4番目の並び替えVBA(ユーザー定義の順番を設定シートの一覧表から取得)が使われています。

並び替えVBAの動作

1.並び替えVBA(設備番号を昇順にする方法)

サンプルVBA

1	Sub テスト()
2	gyo = Cells(Rows.Count, 3).End(xlUp).Row
3	Range(Cells(1, 1), Cells(gyo, 3)).Sort Key1:=Cells(1, 3), Order1:=xlAscending, Header:=xlYes
4	End Sub

実行結果

このような単純な並び替えはたった4行のプログラムで完了します。

2.並び替えVBA(枝番を認識する方法)

仕事で使われている一覧が全て整数であるとは限りませんよね。番号に枝番がある表の並び替えはVBAを使わないとうまくいきません。枝番を判別して並び替えを行うVBAを紹介します。

枝番とは、番号「1」の次が「2」ではなく「1-1」や｢1-2｣となること。なぜこんなわかりくくするの？と思うかもしれませんが、仕事ではよくあることです。

番号「1」の商品の類似品が新発売した場合、番号「2」をつけたいところだが、番号「2」は別の商品で使っている。。こんなとき枝番「1-1」が新商品に付けられます。

枝番は並び替えにはとても厄介な表記だが、とても便利なものなのです。なので枝番の並び替えをマスターすれば仕事で間違いなく役立てることができます。

サンプルVBA

1	Sub テスト()
2	retu = Cells(1, Columns.Count).End(xlToLeft).Column
3	gyo = Cells(Rows.Count, 1).End(xlUp).Row
4	On Error Resume Next
5	For i = 2 To gyo
6	ds = InStr(Cells(i, 3), “-“)
7	df = Mid(Cells(i, 3), 1, ds – 1)
8	If InStr(Cells(i, 3), “-“) <> 0 Then
9	Cells(i, retu + 1) = df
10	Else
11	Cells(i, retu + 1) = Cells(i, 3)
12	End If
13	Next
14	Range(Cells(1, 1), Cells(gyo, retu + 1)).Sort Key1:=Cells(1, retu + 1), Order1:=xlAscending, Header:=xlYes
15	Cells(1, retu + 1).EntireColumn.ClearContents
16	End Sub

実行結果

枝番の識別のは関数InStr と関数Midを使っています。

枝番の認識VBA

6行目で、枝番｢-｣の位置を｢ds」に入れる

7行目で、テキストの始まりから枝番｢-｣の一つ前までのテキストを｢df｣にいれる

関数InStrと関数Midの組み合わせはテキストを操作するときによく使われます。

3.ユーザー定義を使った並び替えVBA(プログラムに直接並び替えたい項目の順番を記載)

サンプルVBA

1	Sub テスト()
2	With ActiveSheet.Sort
3	.SortFields.Clear
4	.SortFields.Add Key:=Cells(1, 1), Order:=xlAscending, CustomOrder:="北海道,青森,東京,大阪,鹿児島,沖縄"
5	.SetRange Range(“A1").CurrentRegion
6	.Header = xlYes
7	.Apply
8	End With
9	End Sub

実行結果

上記のVBAの紹介で使っているもので、地域を「北海道→青森→大阪→鹿児島→沖縄」の順番に並び替えています。

CustomOrder:="〇〇,〇〇"の〇〇を付け加えたり、変更したりするだけでユーザー定義の設定完了。

4.ユーザー定義を使った並び替えVBA(並び替えたい項目の一覧表から順番を取得する)

サンプルVBA

1	Sub テスト()
2	Dim Ash As Worksheet
3	Set Ash = Sheets(“一覧")
4	Dim Bsh As Worksheet
5	Set Bsh = Sheets(“設定")
6	Dim 配列() As String
7	Dim i As Integer
8	ReDim 配列(7)
9	For i = 0 To 7
10	配列(i) = Bsh.Cells(i + 2, 1)
11	Next i
12	With Ash.Sort
13	.SortFields.Clear
14	.SortFields.Add Key:=Cells(1, 1), Order:=xlAscending, CustomOrder:=Join(配列, “,")
15	.SetRange Range(“A1").CurrentRegion
16	.Header = xlYes
17	.Apply
18	End With
19	End Sub

実行結果

設定シートの一覧表からユーザー定義の項目を取得するのでシート名の取得VBA使っています。

シートの設定VBAについて下記の記事で詳しく紹介しています。

mamemametochan.com

シート名を取得するVBAの設定【シートを区別することで作業効率アップ】

https://mamemametochan.com/excel-2

豆父ちゃんVBAでシート名を取得する方法が知りたい。こんな疑問にお答えします。結論からいうと、オブジェクト型変数を使ってシート名を宣言する！です。オブジェクト型変数と言われてもよくわからない！という方がほとんどですよね。実際のVBAではオブジェクト型変数を設定することになりますが、オブジェクト型変数を深く理解していなくてもできるシート名の取得方法をご紹介していきます。難しいことはいいから、VBAでシートを区別したい別シートに情報を転記するVBAを設定したいシート...

他にも Join関数(取得した文字列データは連結する)を使っていたり少し難しいVBAになっています。サンプルを使っていろいろ試しながら覚えていきましょう。

並び替えVBA(ユーザー設定の基本な使い方)

ユーザー設定の並び替えは順番を自分で設定できる！という特徴があります。

都道府県を北から順番に並び替えたい
市町村名を東から順番に並び替えたい
商品名の順番を自由に並び替えられたい

このようなときにユーザー定義を設定すれば思い通りの並び替えが可能になります。

ユーザー定義を使った並び替えVBAの作り方

ユーザー定義による並べ替えVBAは基本構造の中のOrderCustomではなく、SortFieldsコレクションを使用します。OrderCustomでやろうとしてもどうしてもうまくいきませんでした。

SortFieldsの基本構造

SortFields.Add ( Key , order , Customorder , dataoption )

各プロパティは上記(Sortメソッド)で説明した内容と変わりありません。なぜSortメソットとSortFieldsを使い分けなければいけないのか理解できていません。

ユーザー定義を使った並び替えVBAを作るときはSortFieldsを使うもの！と自分の中で納得してVBAを開発しています。

下記のSortFieldsを使った基本サンプルを参考にしてみてください。

1	With ActiveSheet.Sort	←アクティブシートにSortFieldオブジェクトを入れていくのでWithを使って簡略化する
2	.SortFields.Clear	現在設定されているフィルターを解除
3	.SortFields.Add Key:=Cells(1, 1), Order:=xlAscending, CustomOrder:=" 〇〇,〇〇,〇〇 “	基本構造の内容
4	.SetRange Range(“A1").CurrentRegion	並び替えの範囲を指定(CurrentRegionは全体を指定するもの)
5	.Header = xlYes	タイトルの有無を指定
6	.Apply	SortFieldsを実行させるもの(SortFieldを使う場合必ず必要)
7	End With	Withの終了