空白行も含めてソートしたい

セルA1 に CommandButton1（ActiveＸ）を作り、下記のコードを割り当てています。（A列はすべてコマンドボタン専用セル）
A列以外すべてのセルはデータ入力セルとし、B 列 ・・C 列・・・というようにデータを列方向に増やしていきます。
各列ごとに複数行のデータが入力されますが、データ行が連続してない（空白行がある）と、データを並べ替えたときにソートの対象とならないようです。
データが不連続でも列全体がソートの対象となるよう、下記コードの修正をお願いできないでしょうか。

Private Sub CommandButton1_Click()

ActiveSheet.Unprotect

Cells.Select
ActiveWorkbook.Worksheets("語彙").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("語彙").Sort.SortFields.Add Key:=Range(Range("b1"), Cells(1, Columns().Count).End(xlToLeft)), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("語彙").Sort
.SetRange Range("b1").CurrentRegion
.Header = xlGuess
.MatchCase = False
.Orientation = xlLeftToRight
.SortMethod = xlPinYin
.Apply
End With

ActiveSheet.Protect

End Sub

No.2 ベストアンサー 回答者： 白亜気 回答日時： 2022/02/02 11:03

ソートの対象にする領域の設定をするだけで出来ます。

標準モジュールに次のように書いても、出来ます。

Sub 空白を含む特定域の並べ替え()

With Sheets(1).Sort
With .SortFields
.Clear
.Add Key:=Range("b3"), SortOn:=xlSortOnValues, Order:=xlAscending
'　　B3のセルを並べ替えの基準にして、昇順で並べ替えるための準備設定
End With
.SetRange Range(Range("b3"), Range("A1").SpecialCells(xlLastCell))
'　　B3から、データがある（右方向、下方向）の最も遠いセルまでを、空白も含めて
'　　並べ替えの対象範囲に設定する
.Orientation = xlLeftToRight
'　　並べ替えの方向を、左から右にするように設定
.Apply
'　　並べ替えの実行
End With

End Sub

この回答へのお礼

ご回答ありがとうございます。

おかげさまで上手くいったようです。

SpecialCells(xlLastCell)

が大変参考になりました。

お礼日時：2022/02/02 18:21

No.3 回答者： fujillin 回答日時： 2022/02/02 18:04

No1です。

＞常に最大のデータ範囲（途中の空白行も含めて）を選択するコードを知りたいのですが。
基準にできる行や列が存在するなら、最終行、最終列を求める　End(xlup)　や　End(xlToLeft)　を利用する方法で求めれば宜しいでしょう。
（ご提示のコードでも、key指定に使っているのでわかると思いますが）

それも面倒なら
　ActiveSheet.UsedRange
を利用なされば宜しいでしょう。
（一部、空白セルを余分に含む場合がありますけれど・・）

この回答へのお礼

ご回答ありがとうございます。

御礼が遅れて申し訳ありません。
なにしろド素人なもので、ご迷惑おかけします。

ActiveSheet.UsedRange

というのも使えるのですね。
色々と参考になりました。
また、よろしくお願いいたします。

お礼日時：2022/02/02 18:42

No.1 回答者： fujillin 回答日時： 2022/02/02 00:57

こんばんは

＞データ行が連続してない（空白行がある）と、データを並べ替えた
＞ときにソートの対象とならないようです。
範囲の指定を、
　＞Range("b1").CurrentRegion
としているので、そうなるだけだなのでは？
値のあるセルが連続している範囲を指定していますよね？

＞データが不連続でも列全体がソートの対象となるよう、
＞下記コードの修正をお願いできないでしょうか。
どの範囲を指定したいのか、何も情報がないので修正のしようもありません。

例えば、B1:G100 の範囲を指定したいのなら、
　Range("B1:G100")
にしておけば良いとか・・


ご質問には関係ありませんが、書き方は自由とは言え、同じ対象の内容を、Withの外で書いたり中で書いたりとわかりにくくありませんか？

この回答へのお礼

ご回答ありがとうございます。

＞どの範囲を指定したいのか、何も情報がないので修正のしようもありません。
：
データが増えていくので、範囲は指定できません。
常に最大のデータ範囲（途中の空白行も含めて）を選択するコードを知りたいのですが。

お礼日時：2022/02/02 17:35