エクセルのvba(最終行を取得する並び替え)

質問

初めまして、エクセルのvbaについて質問をさせてください。
マクロの記録を使って、以下の通りF列→M列→J列の順に優先して、A列からAL列を昇順に並び替えるvbaを作成したのですが、10000行までとう不恰好な書き方になっています。最終行までという書き方に変えたいのですが、色々試したもののうまくいきません…！この場合、最終行を取得するにはどのような書き方にすれば良いのでしょうか…？？(T-T)

'並び替え
ActiveWorkbook.Worksheets("当月").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("当月").Sort.SortFields.Add Key:=Range("F2:F10000"_
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortTextAsNumbers
ActiveWorkbook.Worksheets("当月").Sort.SortFields.Add Key:=Range("M2:M10000"_
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("当月").Sort.SortFields.Add Key:=Range("J2:J10000"_
), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("当月").Sort
.SetRange Range("A1:AL10000")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

回答No.1

real beatin（@realbeatin） · ベストアンサー

こんにちは。

最終行を求める方法は幾つかありますが、今回紹介するのは、
　連続領域の最後のセルの行位置
です。
イメージし易いように、手作業で行った場合の手順
　[A1]を選択
　Ctrl+A キーを押して連続領域を選択
　Ctrl+Shift+Enter キーを押して連続領域の最後のセルを選択
　この時[名前ボックス]（数式バー左）に表示されるアドレスを見て
[AL10000]のように表示された数字部分が、求めたい最終行になります。

最終行を求めた数値を一旦"変数"（nLastRow）に格納します。
求めた最終行を
　"F2:F" & nLastRow
のような形で、セル範囲参照文字列に連結します。
With ステートメントを整理して、見易く書き換えます。
同じ"オブジェクト"を参照する記述が断続的に並んだ場合には、
コードを読み易く → 後から編集し易く、
且、多少のパフォーマンス向上を望める書き方です。
"With ステートメント"、"With ブロック"、"With フレーズ"
など、それぞれ"VBA "とアンド検索する等して
調べて確認しておいてください。

' ' //
Sub Re9336028a()
Dim nLastRow As Long
　　With ActiveWorkbook.Worksheets("当月").Range("A1").CurrentRegion
　　　　nLastRow = .Cells(.Cells.Count).Row
　　End With
　　With ActiveWorkbook.Worksheets("当月").Sort
　　　　With .SortFields
　　　　　　.Clear
　　　　　　.Add Key:=Range("F2:F" & nLastRow), SortOn:=xlSortOnValues, _
　　　　　　　　Order:=xlAscending, DataOption:=xlSortTextAsNumbers
　　　　　　.Add Key:=Range("M2:M" & nLastRow), SortOn:=xlSortOnValues, _
　　　　　　　　Order:=xlAscending, DataOption:=xlSortNormal
　　　　　　.Add Key:=Range("J2:J" & nLastRow), SortOn:=xlSortOnValues, _
　　　　　　　　Order:=xlAscending, DataOption:=xlSortNormal
　　　　End With
　　　　.SetRange Range("A1:AL" & nLastRow)
　　　　.Header = xlYes
　　　　.MatchCase = False
　　　　.Orientation = xlTopToBottom
　　　　.SortMethod = xlPinYin
　　　　.Apply
　　End With
End Sub
' ' //

因みに、今回の課題の例のように、
　１行めがタイトル行、の場合の、並べ替え
については、
仮に、最終行を指定することなく、
それぞれ列全体を範囲として指定しても、
処理の過程、処理の結果、
すべて全く同じことになりますので、
省略した形で、以下のような書き方も出来ます。

' ' //
Sub Re9336028()
　　With ActiveWorkbook.Worksheets("当月").Sort
　　　　With .SortFields
　　　　　　.Clear
　　　　　　.Add Key:=Range("F:F"), SortOn:=xlSortOnValues, _
　　　　　　　　Order:=xlAscending, DataOption:=xlSortTextAsNumbers
　　　　　　.Add Key:=Range("M:M"), SortOn:=xlSortOnValues, _
　　　　　　　　Order:=xlAscending, DataOption:=xlSortNormal
　　　　　　.Add Key:=Range("J:J"), SortOn:=xlSortOnValues, _
　　　　　　　　Order:=xlAscending, DataOption:=xlSortNormal
　　　　End With
　　　　.SetRange Range("A:AL")
　　　　.Header = xlYes
　　　　.MatchCase = False
　　　　.Orientation = xlTopToBottom
　　　　.SortMethod = xlPinYin
　　　　.Apply
　　End With
End Sub
' ' //

マクロの記述の内、行末にある" _"（半角スペースとアンダーバー）
の半角スペース１文字分は書き換えることなく、そのままにして下さい。
質問文中にご提示されたサンプルコードは所々スペースが消えていて
"構文エラー"を招くものになっています。

これからのこととして、マクロを扱う時は、
ご自身で何を書いてあるのか解らないものを動かすことがないように、
記述の意味を少しずつでも、確かめるようにしてください。
頑張ってください。

お礼

ご丁寧に回答いただいてありがとうございました(=^x^=)とても助かりました…！

回答No.2

imogasi

http://officetanaka.net/excel/vba/cell/cell10.htm
などを読んでください。
エクセルＶＢＡの基本の中級必須知識。
ある列だけに注目してよい場合（範囲の下辺が凸凹でない場合）では
Sub test04()
lr = Range("A1000").End(xlUp).Row
MsgBox lr
End Sub
が便利かな。
ーーー
ＣurｒｅｎｔＲｅｇｉｏｎ法
Sub test3()
Range("A2").Select
Range("A2").CurrentRegion.Select
'Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
MsgBox Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Address
End Sub
--
UsedRange法
略
ーー
Ｌａｓｔｒｏｗ法、」Ｌａｓｔｃｅｌｌ法など
Ｇｏｏｇｌｅで照会のこと。
ーー
考えている範囲の間に空白行や空白列がないか注意が必要です。
Ｅｎｄ（xlup)では心配いりません。
ーー
Ｇｏｏｇｌｅで「END(xlup)」で照会せよ。
最終行・最終列の取得方法（End,CurrentRegion,SpecialCells,UsedRange）
など、たくさん記事があるよ。

お礼

ご丁寧にありがとうございました★(*^o^*)回答いただけてとてもありがたかったです！