【VBA】名前付き範囲をデータ追加に合わせて動的に拡張する方法（コピペOK）

VBA

2026.04.202026.04.26

【VBA】名前付き範囲をデータ追加に合わせて動的に拡張する方法（コピペOK）

この記事でできること
どんな場面で使う？
1. 前提条件
完成イメージ（Before / After）
手順
基本コード（VBAで名前付き範囲を再定義）
実務版コード
落とし穴
1. VBAで名前付き範囲が動的に拡張できないときの対処法
2. VBAでOFFSET+COUNTAの範囲がずれるときの対処法
FAQ
まとめ
次にやりたくなること

この記事でできること

VBAで 名前付き範囲をデータの増減に合わせて自動的に拡張 できるようになります。

データを追加するたびに名前の管理を開いて範囲を手動修正している方、この記事のコードをコピペすれば 最終行を自動検出して名前付き範囲を一発で再定義 できます。所要時間は5分です。

自分もマスタデータに商品名を追加するたびに名前の管理を開いて範囲を直していた。地味にストレスだったし、修正を忘れてドロップダウンリストに新しい項目が出てこないトラブルも何度もあった。VBAで動的範囲にしてからは、データを追加するだけで名前付き範囲もドロップダウンも勝手に更新されるようになった。この記事で同じ悩みを抱えている方がサクッと解決できればうれしい。

どんな場面で使う？

名前付き範囲の動的拡張が特に役立つのは、以下のような場面です。

ドロップダウンリストの選択肢をマスタに追加するたびに手動で範囲を修正している — 商品名、担当者名、部署名などのマスタに新しい項目を追加したとき、名前の管理を開いて範囲を修正する手間がなくなる
グラフのデータソースをデータ追加のたびに修正している — 名前付き範囲をグラフのソースに指定しておけば、データ追加時にグラフの自動更新が実現できる
VLOOKUP や SUMIF の検索範囲を固定値にしていて、データ追加に対応できない — 動的な名前付き範囲を参照先にすれば、数式の修正が不要になる
月初のマスタ更新ルーティンで複数の名前付き範囲を手動修正している — 一括更新マクロで3つも4つも名前を一発で最新化できる

前提条件

項目	内容
Excel	2016以降 / Microsoft 365
OS	Windows 10 / 11
保存形式	.xlsm（マクロ有効ブック）
貼り付け場所	標準モジュール
実行方法	マクロ実行 / ボタン割り当て

バックアップ推奨: 実行前にブックのコピーを保存してください。Names.Add は同名の名前付き範囲を上書きします。

—

完成イメージ（Before / After）

Before: A列に商品名が10件。名前付き範囲「商品リスト」は A2:A11 で固定。11件目を追加しても名前付き範囲には含まれない。

After: マクロ実行で名前付き範囲が A2:A13 に自動拡張。ドロップダウンリストにも追加した商品が即座に反映される。

—

手順

開発タブ を表示する（表示されていない場合は「ファイル → オプション → リボンのユーザー設定」で「開発」にチェック）
Alt + F11 でVBE（コードを書く画面）を開く
「挿入」→「標準モジュール」 をクリック
下のコードをコピーして貼り付ける
シートに戻り、Alt + F8 でマクロを選んで実行する
Ctrl + F3（名前の管理）を開いて、範囲が更新されていることを確認する

—

基本コード（VBAで名前付き範囲を再定義）


Sub UpdateNamedRange()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim rng As Range

    Set ws = ThisWorkbook.Sheets("マスタ")

    '--- A列の最終行を取得（1行目は見出し）
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    '--- データがなければ終了
    If lastRow < 2 Then
        MsgBox "データがありません。", vbExclamation
        Exit Sub
    End If

    '--- 名前付き範囲を再定義（A2からA列の最終行まで）
    Set rng = ws.Range("A2:A" & lastRow)
    ThisWorkbook.Names.Add Name:="商品リスト", RefersTo:=rng

    MsgBox "「商品リスト」を A2:A" & lastRow & " に更新しました。"
End Sub

ポイント:

Names.Add は同名の名前付き範囲が既に存在する場合、自動的に上書きする。「既存の名前を削除してから追加」という2段階の処理は不要です
RefersTo に Range オブジェクトを渡すと、シート名も含めて正しく参照が設定される。文字列で "=マスタ!A2:A10" のように渡す方法もあるが、シート名にスペースや記号が含まれる場合に参照式が壊れるリスクがある。Range オブジェクトを使えばExcelが自動でエスケープしてくれるので安全です
1行目を見出しとして除外し、2行目からデータ範囲とする。見出し行を含めてしまうと、ドロップダウンリストの選択肢に「商品名」という見出しテキストが混ざってしまうので注意

最終行の取得テクニックはデータの最終行・最終列を正確に取得する方法で詳しく解説しています。名前付き範囲の基本操作は名前定義（名前の管理）を一括作成・削除する方法を参照してください。

—

実務版コード

実務版1: OFFSET+COUNTA数式で動的範囲を定義

VBAを毎回実行しなくても、数式ベースで自動拡張する名前付き範囲を定義します。一度実行すれば、以後はデータを追加するだけで範囲が自動的に伸びます。


Sub CreateDynamicNamedRange()
    Dim ws As Worksheet
    Dim formula As String

    Set ws = ThisWorkbook.Sheets("マスタ")

    '--- OFFSET+COUNTA数式で動的範囲を定義
    '--- マスタ!$A$2 を起点に、COUNTA(マスタ!$A:$A)-1 行分の範囲
    formula = "=OFFSET(マスタ!$A$2,0,0,COUNTA(マスタ!$A:$A)-1,1)"

    ThisWorkbook.Names.Add Name:="商品リスト", RefersTo:=formula

    MsgBox "動的名前付き範囲「商品リスト」を作成しました。" & vbCrLf & _
           "数式: " & formula
End Sub

数式の仕組み:

OFFSET(マスタ!$A$2, 0, 0, COUNTA(マスタ!$A:$A)-1, 1) は「A2を起点に、A列のデータ件数-1（見出し分を引く）行分の範囲」を返す。OFFSETの引数は順に「起点セル、行方向のずらし、列方向のずらし、高さ、幅」です
データを追加すると COUNTA の値が増え、範囲が自動的に伸びる。つまりVBAを再実行しなくても、セルにデータを入力するだけで名前付き範囲が拡張される仕組みです
VBAの実行は初回の1回だけでOK。ただし、A列の途中に空白セルがある場合は COUNTA のカウントがずれるため、この数式は「途中に空白がないデータ列」で使うのが前提です

実務版2: 複数の名前付き範囲を一括更新

マスタシートに複数の列（商品名・担当者・部署など）があり、それぞれに名前付き範囲を設定するケースです。


Sub UpdateMultipleNamedRanges()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim nameList As Variant
    Dim colList As Variant
    Dim i As Long
    Dim rng As Range
    Dim updatedCount As Long

    Set ws = ThisWorkbook.Sheets("マスタ")

    '--- 名前付き範囲と対応する列を配列で定義
    '--- ここを自分のブックに合わせて変更する
    nameList = Array("商品リスト", "担当者リスト", "部署リスト")
    colList = Array("A", "B", "C")

    For i = LBound(nameList) To UBound(nameList)
        '--- 各列の最終行を取得
        lastRow = ws.Cells(ws.Rows.Count, colList(i)).End(xlUp).Row

        '--- データがなければスキップ
        If lastRow < 2 Then
            Debug.Print nameList(i) & ": データなし（スキップ）"
        Else
            '--- 名前付き範囲を再定義
            Set rng = ws.Range(colList(i) & "2:" & colList(i) & lastRow)

            On Error Resume Next
            ThisWorkbook.Names.Add Name:=CStr(nameList(i)), RefersTo:=rng
            If Err.Number = 0 Then
                updatedCount = updatedCount + 1
                Debug.Print nameList(i) & ": " & rng.Address & " に更新"
            Else
                Debug.Print nameList(i) & ": 更新失敗 - " & Err.Description
                Err.Clear
            End If
            On Error GoTo 0
        End If
    Next i

    MsgBox updatedCount & " 個の名前付き範囲を更新しました。"
End Sub

自分はこの一括更新を月初のマスタ更新ルーティンに組み込んでいる。3つの名前付き範囲を手で直す作業がなくなって、更新漏れも起きなくなった。

実務版3: ドロップダウンリスト連動（名前付き範囲 + 入力規則）

名前付き範囲を更新した上で、その範囲を使ったドロップダウンリスト（入力規則）も再設定します。


Sub UpdateNamedRangeAndDropdown()
    Dim wsMaster As Worksheet
    Dim wsInput As Worksheet
    Dim lastRow As Long
    Dim rng As Range
    Dim dropdownRange As Range

    Set wsMaster = ThisWorkbook.Sheets("マスタ")
    Set wsInput = ThisWorkbook.Sheets("入力シート")

    '--- マスタのA列から最終行を取得
    lastRow = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row

    If lastRow < 2 Then
        MsgBox "マスタにデータがありません。", vbExclamation
        Exit Sub
    End If

    '--- 名前付き範囲を更新
    Set rng = wsMaster.Range("A2:A" & lastRow)
    ThisWorkbook.Names.Add Name:="商品リスト", RefersTo:=rng

    '--- ドロップダウンリストの設定先（入力シートのB2:B100）
    Set dropdownRange = wsInput.Range("B2:B100")

    '--- 既存の入力規則をクリアしてから再設定
    On Error Resume Next
    dropdownRange.Validation.Delete
    On Error GoTo 0

    '--- 名前付き範囲を使ってドロップダウンリストを設定
    dropdownRange.Validation.Add _
        Type:=xlValidateList, _
        AlertStyle:=xlValidAlertStop, _
        Formula1:="=商品リスト"

    MsgBox "名前付き範囲とドロップダウンリストを更新しました。" & vbCrLf & _
           "範囲: A2:A" & lastRow
End Sub

入力規則の設定方法をもっと知りたい方は入力規則（ドロップダウンリスト）をVBAで一括設定する方法を参照してください。

—

落とし穴

#	症状	原因	対策
1	名前付き範囲が更新されない	シート名にスペースや記号が含まれていて参照式が壊れている	シート名はシンプルな名前にするか、`RefersTo` に `Range` オブジェクトを渡す（文字列ではなく）
2	OFFSET+COUNTA数式で空白セルがあると範囲がずれる	`COUNTA` は空白セルをカウントしないため、途中に空白があると実際のデータ件数と合わない	途中に空白がある場合はVBAで `End(xlUp)` を使って最終行を取得する方法を使う
3	ドロップダウンリストに新しい項目が表示されない	名前付き範囲は更新したが、入力規則の再設定を忘れている	名前付き範囲の `RefersTo` を更新すれば入力規則も自動追従する。ただし、OFFSET数式を使わずに固定範囲で定義している場合は入力規則の再設定が必要
4	「実行時エラー ‘1004’: その名前は正しくありません」	名前にスペースや特殊文字（`!`, `?`, `-` など）が含まれている	名前は英数字・アンダースコア・日本語のみ使用する。先頭を数字にしない
5	名前付き範囲がブックスコープではなくシートスコープになる	`ws.Names.Add` のようにシートオブジェクトから追加すると、そのシート限定の名前になる	ブック全体で使いたい場合は `ThisWorkbook.Names.Add` を使う
6	`#REF!` エラーが名前の管理に表示される	参照先のシートが削除された / シート名が変更された	不要な名前付き範囲は `ThisWorkbook.Names("名前").Delete` で削除する。一括削除は名前定義（名前の管理）を一括作成・削除する方法を参照

自分もOFFSET+COUNTA数式で動的範囲を作ったのに、マスタの途中に空白行が1行あって範囲がずれていることに気づかず、ドロップダウンから最後の2件が消えていたことがある。空白行がないことを前提にするか、VBAで最終行を取得する方法を使うかは最初に決めておいた方がいい。

VBAで名前付き範囲が動的に拡張できないときの対処法

「Names.Addで名前付き範囲を再定義したのに、ドロップダウンリストに新しい項目が反映されない」という場合、原因はNames.Addの対象がシートスコープになっていることが多い。ws.Names.Add のようにシートオブジェクトから追加すると、そのシート限定の名前になり、別シートの入力規則からは参照できない。対処法は ThisWorkbook.Names.Add を使ってブックスコープで定義すること。自分も最初これに気づかず、ドロップダウンが更新されない原因を1時間探した。

VBAでOFFSET+COUNTAの範囲がずれるときの対処法

「OFFSET+COUNTA数式で動的範囲を定義したのに、最後の数件がドロップダウンに表示されない」という場合、原因はデータ列の途中に空白セルがあることだ。COUNTAは空白セルをカウントしないため、途中に空白が1つあると範囲が1行分短くなる。対処法は2つある。1つ目は空白行を事前に削除してからOFFSET数式を使う方法。2つ目はOFFSET数式を諦めて、VBAの End(xlUp).Row で最終行を取得して Names.Add で再定義する方法だ。途中に空白がある可能性があるならVBA版が確実。

—

FAQ

Q1. OFFSET+COUNTA数式とVBAでの再定義、どちらを使うべき？

データの途中に空白がなく、列が1つだけなら OFFSET+COUNTA が手軽です。複数列の名前付き範囲を管理する場合や、途中に空白がある可能性があるなら VBAでの再定義 が確実です。自分は最初OFFSET数式を使っていたが、空白行問題でVBA版に切り替えた。

Q2. 名前付き範囲のスコープ（ブック全体 vs シート限定）を変更するには？

VBAで作り直すのが最も確実です。


'--- シートスコープの名前を削除してブックスコープで再作成
On Error Resume Next
ThisWorkbook.Names("商品リスト").Delete
On Error GoTo 0
ThisWorkbook.Names.Add Name:="商品リスト", RefersTo:=Range("マスタ!A2:A10")

Q3. 名前付き範囲を使ったドロップダウンリストが「元の値を入力してください」エラーになる

Formula1 に渡す名前の前に = が必要です。Formula1:="=商品リスト" と書いてください。= がないと文字列として解釈されます。

Q4. テーブル（ListObject）をデータソースにすれば名前付き範囲は不要？

テーブルの構造化参照（例: テーブル1[商品名]）を使えば、名前付き範囲を定義しなくても動的な範囲参照ができます。ただし、入力規則の Formula1 にテーブル参照を直接使えない制約があるため、名前付き範囲を経由する方法が確実です。テーブル操作の詳細はテーブル形式のデータをVBAで操作する方法を参照してください。

Q5. 既存の名前付き範囲の一覧をVBAで取得するには？


Dim nm As Name
For Each nm In ThisWorkbook.Names
    Debug.Print nm.Name & " → " & nm.RefersTo
Next nm

イミディエイトウィンドウ（Ctrl + G）に一覧が出力されます。

—

まとめ

この記事では、VBAで 名前付き範囲をデータ追加に合わせて動的に拡張する方法 を解説しました。

基本: Names.Add + End(xlUp) で名前付き範囲を最終行まで再定義
数式版: OFFSET+COUNTA で一度設定すれば自動拡張する動的範囲を作成
一括更新: 配列で複数の名前付き範囲をまとめて更新
ドロップダウン連動: 名前付き範囲の更新 + 入力規則の再設定で、リスト内容が自動的に最新化

データを追加するたびに名前の管理を手動で修正するストレスから解放されます。

名前付き範囲の動的拡張は地味な機能に見えますが、「ドロップダウンリストが更新されない」「グラフに新しいデータが反映されない」「VLOOKUPの検索範囲が足りない」といった問題の根本原因になっていることが多いです。この記事の基本コードを1つ組み込んでおくだけで、データ追加のたびに発生していた手動メンテナンスがまとめて解消されます。自分の場合、商品マスタに月10件ほど新商品が追加される運用だったが、名前付き範囲を動的にしてからは追加作業後に一切手を加えなくて済むようになった。特に複数の名前付き範囲を管理しているブックでは、一括更新マクロの効果が大きい。OFFSET数式版とVBA再定義版のどちらが自分の運用に合うかを判断して、最初の一歩を踏み出してみてください。

—

次にやりたくなること

名前付き範囲をもっと詳しく管理したい → 名前定義（名前の管理）を一括作成・削除する方法で名前の一括削除・一覧出力まで解説しています
ドロップダウンリストを本格的にカスタマイズしたい → 入力規則（ドロップダウンリスト）をVBAで一括設定する方法で連動ドロップダウンや動的リストの作り方を解説しています
テーブル形式でデータを管理したい → テーブル形式のデータをVBAで操作する方法でテーブルの追加・集計・フィルタ操作をVBAで自動化できます
名前付き範囲の元データの最終行を正確に取得したい → データの最終行・最終列を正確に取得する方法で End(xlUp) の仕組みと注意点を深掘りしています
名前付き範囲をグラフのデータソースに使って自動更新したい → グラフのデータ範囲を自動更新する方法で動的範囲とグラフの連携を解説しています