Excel VBAでデータを操作する際に、行数が固定されていないケースは非常に多いです。例えば「日々売上データが追加される表」や「社員名簿が更新される表」など、行の長さは日ごとに変化します。
こうした場合に固定範囲を指定してしまうと、データが途中までしか処理されなかったり、余分な空白を含んでしまうリスクがあります。
そこで役立つのが 最終行を取得して動的に範囲を指定する方法 です。本記事では、動的範囲を使ってフィルタと並び替えを組み合わせる実用的なテクニックを紹介します。
1. 最終行を取得する方法
最終行を求めるためには、Cells(Rows.Count, 列番号).End(xlUp).Row という構文を使います。
例えば、A列にデータが入っている場合の最終行取得は以下の通りです。
Dim lastRow As Long
lastRow = Cells(Rows.Count, 1).End(xlUp).Row 'A列の最終行を取得Rows.Countはシートの最大行数（Excelのバージョンによって1048576行）End(xlUp)は「Ctrl + ↑」と同じ動きで、最後にデータが入力されているセルを見つけます。
2. 動的範囲でフィルタをかける
例えば「A列に部署名、B列に売上、C列に担当者名」があるデータを想定します。
最終行を取得したうえで、A列「営業部」だけに絞り込む例は次の通りです。
Sub 動的範囲でフィルタ()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 'A列の最終行を取得
'既存のフィルタを解除
If ws.AutoFilterMode Then ws.AutoFilterMode = False
'ヘッダーを含めて範囲を指定
ws.Range("A1:C" & lastRow).AutoFilter Field:=1, Criteria1:="営業部"
End Subこのように最終行を使えば、行数が増減しても自動的に正しい範囲がフィルタ対象になります。
3. 動的範囲で並び替えを行う
次に、フィルタした結果を売上（B列）で降順に並び替える例を紹介します。
Sub 動的範囲でフィルタ並び替え()
Dim ws As Worksheet
Dim lastRow As Long
Set ws = ThisWorkbook.Sheets("Sheet1")
lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row 'A列の最終行を取得
'既存のフィルタを解除
If ws.AutoFilterMode Then ws.AutoFilterMode = False
'範囲を指定してフィルタ（営業部だけ）
ws.Range("A1:C" & lastRow).AutoFilter Field:=1, Criteria1:="営業部"
'SortFieldsをクリアしてから設定
ws.Sort.SortFields.Clear
ws.Sort.SortFields.Add Key:=ws.Range("B2:B" & lastRow), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ws.Sort
.SetRange ws.Range("A1:C" & lastRow)
.Header = xlYes
.Apply
End With
End Subポイント
SortFields.Clearを必ず実行することで、前回のソート条件を引きずらずに済む。Range("A1:C" & lastRow)のように、動的に範囲を指定することでデータが増えても問題なし。Header:=xlYesを指定しておくと、1行目を見出しとして認識し、ソート対象から外してくれる。
4. 実務での応用例
- 売上管理表で「当月データの最終行」まで自動で集計し、営業部ごとに並べ替え
- 社員リストを「部署でフィルタ → 入社日の古い順に並び替え」
- 日々追加される在庫データを「商品カテゴリで絞り込み → 在庫数の少ない順に並べ替え」
いずれも 「データ件数が変動する」状況で威力を発揮 します。
5. まとめ
- 最終行取得で 動的な範囲指定 が可能になる
- フィルタとソートを組み合わせることで、欲しいデータをすぐに整理できる
SortFields.ClearとHeader:=xlYesを忘れないことが安定した処理のコツ
このテクニックを使えば、定型処理を自動化して「データ量に左右されない安定したマクロ」を作成できます。
コメント