この記事でできること
検査データに通し番号を振っている。データ数が毎日変わる。昨日は50件、今日は73件。毎回最終行を確認して、次の番号を手入力する。1つずれると以降全部ずれる。しかも月が変わると番号体系もリセットしたい。「2026-04-001」「2026-04-002」のように年月付きの連番にしているが、月初に手動で「001」に戻すのを忘れて先月の続きから振ってしまったことがある。
VBAで自動連番にしてからは、データを追加するたびに自動で次の番号が振られる。月が変われば自動でリセット。番号のずれも重複もゼロ。
- 対象：行を追加・削除するたびに連番を手作業で振り直している人、VBAが初めての人
- 所要時間：コピペ → 動作確認まで約5分（目安）
どんな場面で使う？
- 品質管理 — 検査データに「2026-04-001」形式の通し番号を振っている。データ数が毎日変わるので手入力だとずれる。月替わりのリセットも自動にしたい
- 事務 — 管理台帳や顧客リストで行を追加・削除するたびに連番を手で振り直している。報告書に番号を振っているが、行削除で抜け番が発生して指摘される
- 経理 — 伝票番号や受付番号を001形式の書式付き連番で自動採番したい。手入力だと重複や抜けが発生する
- 製造 — 製造ロット番号をシート上でデータを編集するたびに自動で更新してほしい
最終行の取得（Cells.End(xlUp).Row）の仕組みが分からない場合は、先に最終行を取得する方法を読んでおくと理解がスムーズ。
—
完成イメージ（Before / After）
Before（実行前）
行の削除後、A列の連番が飛んでいる（3番が抜けている）。
| A（No.） | B（名前） | C（部署） | |
|---|---|---|---|
| 1 | No. | 名前 | 部署 |
| 2 | 1 | 田中 | 営業部 |
| 3 | 2 | 鈴木 | 総務部 |
| 4 | 4 | 佐藤 | 製造部 |
| 5 | 5 | 山田 | 品質管理部 |
After（実行後）
連番が1から振り直され、抜け番がなくなった。
| A（No.） | B（名前） | C（部署） | |
|---|---|---|---|
| 1 | No. | 名前 | 部署 |
| 2 | 1 | 田中 | 営業部 |
| 3 | 2 | 鈴木 | 総務部 |
| 4 | 3 | 佐藤 | 製造部 |
| 5 | 4 | 山田 | 品質管理部 |
—
実行前の準備
バックアップを取る
既存のデータが入っているExcelファイルで試す場合は、先にファイルをコピーしてバックアップを取ること。連番の上書きは元に戻せない。
Excelをマクロ有効ブック（.xlsm）で保存する
- Excelを開く（新規でも既存でもOK）
- 「ファイル」→「名前を付けて保存」
- ファイルの種類を 「Excel マクロ有効ブック (*.xlsm)」 に変更して保存
.xlsx のままだとマクロが保存されない。必ず .xlsm にすること。
シートのレイアウトを確認する
| A | B | C | |
|---|---|---|---|
| 1 | No. | 名前 | 部署 |
| 2 | （連番が入る） | 田中 | 営業部 |
- A列: 連番を入れる列
- B列: データが入っている列（この列で最終行を判定する）
- 1行目: ヘッダー行（連番の対象外）
—
手順（コピペ → 動作確認まで約5分）
VBE（コードを書く画面）を開く
Alt + F11 キーを押すとVBE（Visual Basic Editor）が開く。
一般的にはAlt + F11で開けるが、企業のセキュリティ設定でVBAが無効化されている場合は、IT部門に確認すること。
標準モジュールを作成する（最小版・実務版の場合）
- VBE のメニューから「挿入」→「標準モジュール」をクリック
- 右側にコードウィンドウが開く → ここにコードを貼り付ける
コードを貼り付ける
下の「コード（最小版）」をコピーして、標準モジュールのコードウィンドウに貼り付ける。
動作を確認する
- VBEからExcelの画面に戻る（Alt + F11 または タスクバーでExcelをクリック）
- Alt + F8 を押してマクロ一覧を表示
- 「連番を振る」を選択して「実行」をクリック
- A列に連番が振られ、MsgBox で件数が表示されれば成功
—
コード（最小版）– シンプルなFor文で連番を振る
まずはこれだけで動く。A列の2行目からB列の最終行まで、1, 2, 3… と連番を振る。
貼り付け先は標準モジュール
Sub 連番を振る()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
For i = 2 To lastRow
ws.Cells(i, 1).Value = i - 1
Next i
MsgBox (lastRow - 1) & " 件に連番を振りました。"
End Sub
コードの動作：
- B列の最終行を取得する（A列ではなくB列で取るのがポイント。最終行の取得についてはこちら（/032））
- 2行目から最終行までFor文でループ
- A列に
i - 1（= 1, 2, 3…）を代入 - MsgBox（/035）で処理件数を表示
注意： B列にデータがない行にも連番が振られる。空白行をスキップしたい場合は、次の実務版を使う。
—
コード（実務版）– 空白行スキップ + 書式付き連番（001形式）
自分はこの実務版を管理台帳で使ってから、台帳番号の振り直し作業がゼロになった。空白行をスキップして、001, 002, 003… の書式付き連番を振る。
貼り付け先は標準モジュール
Sub 連番を振る_実務版()
Dim ws As Worksheet
Dim lastRow As Long
Dim i As Long
Dim num As Long
Set ws = ActiveSheet
lastRow = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
If lastRow < 2 Then
MsgBox "データがありません。", vbExclamation
Exit Sub
End If
num = 0
For i = 2 To lastRow
If ws.Cells(i, 2).Value <> "" Then
num = num + 1
ws.Cells(i, 1).Value = Format(num, "000")
Else
ws.Cells(i, 1).ClearContents
End If
Next i
MsgBox num & " 件に連番を振りました。"
End Sub
追加ポイント：
- B列が空白の行はスキップし、A列の連番もクリアする
Format(num, "000")で3桁の書式付き連番（001, 002, 003…）を生成- データが1件もない場合はMsgBoxで通知して終了（ガード処理）
- カウンタ
numを別に持つことで、空白行があっても連番が飛ばない
書式を変えたい場合： Format(num, "000") の "000" を変更する。4桁なら "0000"、接頭辞付きなら "A-" & Format(num, "000")。
—
コード（自動版）– Worksheet_Changeイベントで行追加・削除に自動対応
ここからが本題。B列の値が変わるたびに、自動でA列の連番を振り直す。行を追加・削除しても、B列のどこかを編集すれば連番が自動で整う。
Worksheet_Changeイベントの基本はセルの値が変わったら自動実行（/008）で解説しているので、初めての場合は先に読んでおくと理解しやすい。
★ 貼り付け先はシートモジュール（標準モジュールではない）
- VBE の左側のツリーで、対象のシート名（例：Sheet1 (Sheet1)）をダブルクリック
- 右側にコードウィンドウが開く → ここに貼り付ける
- 「挿入」→「標準モジュール」は使わない
' シートモジュールに貼り付け
Private Sub Worksheet_Change(ByVal Target As Range)
Dim lastRow As Long
Dim i As Long
Dim num As Long
' B列の変更時のみ実行
If Intersect(Target, Me.Columns(2)) Is Nothing Then Exit Sub
On Error GoTo ErrHandler
Application.EnableEvents = False
lastRow = Me.Cells(Me.Rows.Count, 2).End(xlUp).Row
num = 0
For i = 2 To lastRow
If Me.Cells(i, 2).Value <> "" Then
num = num + 1
Me.Cells(i, 1).Value = num
Else
Me.Cells(i, 1).ClearContents
End If
Next i
Application.EnableEvents = True
Exit Sub
ErrHandler:
Application.EnableEvents = True
MsgBox "連番の処理中にエラーが発生しました。" & vbCrLf & Err.Description, vbExclamation
End Sub
重要：EnableEvents について
Application.EnableEvents = False は、イベントの発火を一時的に止める命令。これがないとA列への書き込みで再びWorksheet_Changeが発火し、無限ループになる。処理が終わったら必ず True に戻すこと。上記コードでは On Error GoTo ErrHandler を入れているため、エラーが発生しても EnableEvents は確実に True に戻る。万が一無限ループに入った場合の対処法は次のセクションで解説する。
コードの動作：
- B列のセルが変更される → Worksheet_Change が自動で実行される
- B列以外の変更は無視（
Intersectで判定） - EnableEvents を False にして無限ループを防止
- B列にデータがある行だけにA列で連番を振る
- B列が空の行はA列もクリア
- EnableEvents を True に戻して終了
注意： 行を削除しただけではB列の値は変更されないため、Worksheet_Changeは発火しない。行を削除した後、B列のどこかのセルを編集すれば連番が自動で振り直される。
—
もし無限ループに入ったら（対処法）
EnableEvents を書き忘れた場合、Excelがフリーズすることがある。
対処手順：
- Ctrl + Break を押す（多くのキーボードで中断できる）
- Ctrl + Break が効かない場合（ノートPCでBreakキーがない等）→ Ctrl + Alt + Delete →「タスク マネージャー」→ Excel を選択 →「タスクの終了」
- Excelを再起動し、ファイルを開き直す
- VBE（Alt+F11）を開き、「表示」→「イミディエイト ウィンドウ」（Ctrl+G）で以下を入力して Enter：
Application.EnableEvents = True
これでイベントが復旧する。
—
よくある落とし穴5選
| # | 症状 | 原因 | 対策 |
|---|---|---|---|
| 1 | Excelがフリーズする（無限ループ） | Worksheet_ChangeでA列に値を書き込むが、EnableEvents = False を書いていない |
コードの先頭に Application.EnableEvents = False、末尾に True を必ず書く。自分もこれで30分溶かした |
| 2 | Worksheet_Changeのコードを貼り付けたのに動かない | 標準モジュールに貼り付けている。Worksheet_Changeはシートモジュール専用 | VBEの左ツリーで対象シート（例：Sheet1）をダブルクリックして、そこに貼り付ける。最小版・実務版とは貼り付け場所が違う |
| 3 | 連番が001なのにSUMやVLOOKUPで使えない | Format("000") の戻り値は文字列。数値として認識されない |
数値計算に使いたい場合は Format を使わず数値のまま振る。表示だけ3桁にしたい場合はセルの表示形式で NumberFormat = "000" を設定すれば、数値のまま3桁表示できる（例：ws.Cells(i, 1).NumberFormat = "000"） |
| 4 | 行を挿入しても連番が振り直されない | 行を挿入しただけではB列に値が入らず、Worksheet_Changeが発火しない | 行を挿入した後にB列に値を入力すれば発火する。またはWorksheet_SelectionChangeイベントの併用も検討 |
| 5 | 保護シートで連番が振られない | シート保護中はVBAからの書き込みもブロックされる | Protect 時に UserInterfaceOnly:=True を指定すれば、VBAからの書き込みは許可される。詳しくはシート保護の方法（/027） |
—
VBAで連番を振ったのに番号が飛ぶときの対処法
「連番マクロを実行したのに番号が飛ぶ」という場合、原因はA列（連番列）で最終行を取得していることだ。A列に古い連番が残っていると、B列のデータより多い行数を拾ってしまう。対処法は、最終行の取得をB列（データ列）に変更すること。コード内の Cells(ws.Rows.Count, 2) の 2 がB列を指している。
VBAのWorksheet_Changeが動かないときの対処法
「コードを貼り付けたのにWorksheet_Changeが動かない」という場合、原因は貼り付け先が標準モジュールになっていることだ。Worksheet_Changeはシートモジュール専用のイベントなので、VBEの左ツリーで対象シート（例：Sheet1）をダブルクリックして開くコードウィンドウに貼り付ける必要がある。
FAQ
Q1: 連番の開始番号を100からにしたい
実務版・Worksheet_Change版では、連番を代入している部分を num + 99 に変更する。たとえば実務版なら ws.Cells(i, 1).Value = Format(num + 99, "000") とすれば、100, 101, 102… のように振られる。最小版の場合は ws.Cells(i, 1).Value = i - 1 + 99 でも同じ結果になる。
Q2: 連番に接頭辞を付けたい（A-001, B-002など）
Format(num, "000") を "A-" & Format(num, "000") に変更する。ただし文字列になるので数値計算には使えない。
Q3: B列以外のデータ列で最終行を判定したい
Cells(ws.Rows.Count, 2) の 2 を変更する。C列なら 3、D列なら 4。Worksheet_Change版は Me.Columns(2) の 2 も同様に変更する。
Q4: Worksheet_Changeが突然動かなくなった
EnableEventsがFalseのまま残っている可能性が高い。VBEを開き、「表示」→「イミディエイト ウィンドウ」（Ctrl+G）で Application.EnableEvents = True と入力してEnter。これで復旧する。
Q5: 複数シートに同じ連番処理をしたい
シートモジュールのWorksheet_Changeコードを、各シートのシートモジュールにそれぞれコピペする。標準モジュールの実務版なら、複数シートに一括処理する方法（/015）の手法で全シートにループ実行できる。
—
まとめ
この記事で、VBAで自動連番を振り、行の追加・削除にも対応できるようになった。
- 最小版：For文でA列に1, 2, 3…と連番を振る
- 実務版：空白行スキップ + 書式付き連番（001形式）
- 自動版：Worksheet_Changeイベントで、B列変更のたびに連番を自動で振り直す
重要なのは以下の2点：
- 最終行の取得はB列（データ列）で行う（A列で取ると連番が残っている行を拾ってしまう）
- Worksheet_Change版は
EnableEvents = Falseを必ず書く（無限ループ防止）
関連記事
- 【VBA】最終行を正しく取得する方法（/032） —
Cells.End(xlUp).Rowの仕組みと注意点 - 【VBA】セルの値が変わったら自動実行する方法（/008） — Worksheet_Changeイベントの基本を詳しく解説
- 【VBA】複数シートに同じ処理を一括実行する方法（/015） — 全シートに連番処理を一括で適用したい場合
次にやりたくなること
- 【VBA】請求書番号の自動採番（/037） — 連番の仕組みを請求書番号の自動採番に応用したい場合はこちら
- 【VBA】セルの値が変わったら自動実行する方法（/008） — Worksheet_Changeイベントをもっと活用したい場合はこちら
- 【VBA】数値を0埋め（ゼロパディング）して桁を揃える方法（/085） — 連番を001形式で管理番号にしたい場合はこちら
- 【VBA】複数シートに同じ処理を一括実行する方法（/015） — 全シートに連番処理を一括で適用したい場合はこちら
- 【VBA】最終行を正しく取得する方法（/032） — 最終行取得の仕組みをもっと詳しく知りたい場合はこちら
—
もっとカスタマイズしたい場合
「複数シートに一括で設定したい」「採番ルールが複雑（年度＋連番など）」「特定の条件のときだけ連番を振りたい」など、業務に合わせたカスタマイズが必要な場合は、ココナラで相談できます。
相談時に以下の情報があるとスムーズです：
- Excel のバージョン / OS
- 連番のフォーマット（001形式、A-001形式など）
- データの列構成（どの列に連番を振るか、最終行の判定列はどこか）
- 行の追加・削除の頻度と運用方法
コメント