【VBA】シートにあるデータを配列に格納する（空白含む）｜UsedRange

空白を含む？

空白を含んだ、使用されている範囲を取る

シートで使用されているセルの範囲すべてを取得

空白も含む範囲：UsedRange

Public Function GetSheetDataUsed(ByVal ws As Worksheet) As Variant
    GetSheetDataUsed = ws.UsedRange.value
End Function

ただし、UsedRangeにはいろいろ注意点がある

UsedRangeの注意点

1) 一度入れて削除したセルも残る

Root cause｜根本原因

• UsedRange は 値（values） だけではなく セル書式（formats）、数式（formulas）、データの入力規則（data validation）、条件付き書式（conditional formatting）、コメント/メモ など、“使用された”痕跡 を持つセルを「使用済み」と見なす
• 値を消しても 書式や検証ルールが残ったまま やと、そのセルは UsedRange に含まれ続ける
• さらに、コピー貼り付け・大量書式設定・アドイン処理等で「最後のセル（LastCell）」が 過大 に記録され、UsedRange が 膨張しやすい

How to detect｜検出方法

• Debug.Print ws.UsedRange.Address と ws.Cells.SpecialCells(xlCellTypeLastCell).Address を比べて、想定より右/下に伸びてないかチェック
• 見た目は空なのに最終列・最終行が異様に大きいなら膨張している

How to fix｜修復手順（やるなら）

1. 余分な領域を ClearContents ではなく Clear（= 値＋書式）で掃除（値と書式の両方を消す）
2. 不要な Data Validation / Conditional Formatting を削除
3. ws.UsedRange を 一度読む（touch） と内部が再評価されることがある
4. 保存→再オープン（ブック保存で“最後のセル”更新がかかる場合あり）
5. それでも直らなければ、クリーンなシートに必要範囲だけコピー して置き換えるのが最終手段

2) 値が Nothing のセルが配列に混ざる

Correctness｜正しさ

• Range(...).Value で受け取る 2D 配列の空セルは Nothing ではなく Empty（VarType = vbEmpty）。
• Nothing は オブジェクト参照が無い ことを指し、値の空 とは別物

Implication｜影響

• 空判定は IsEmpty(v) または Len(CStr(v)) = 0 等で行う
• If v Is Nothing Then ... は 常に偽 になってバグの温床になる

3) UsedRange はリセットされにくく再現性が低い

Behavior｜挙動

• UsedRange の再計算はイベント的・保存的タイミングに依存し、“いつ正しく縮むか”が予測しづらい
• 書式・検証・コメント等の“非値要素”が最後のセルの決定に影響するため、人や処理手順で結果が揺れる

Best practice（やるなら）

• 「データの実使用範囲」 を取るなら、
  1. 値/数式の実在を Find("*") で縦横に探索（formats を無視）
  2. 見つかった最終行・列で矩形を切り出す
• これで「書式だけ残ってる行列」を除外でき、安定した範囲取得 ができる

たまに、サクッとUsedRangeで取りたい場面がある

参考：代替パターン（Data-only Used Range）

ひと手間かける場合は、ちょっと複雑

Find-based LastCell（検索ベース最終セル）

• 値・数式が存在する本当の最終行/列だけを採用（format 汚染を回避）
• LookIn:=xlFormulas を使うと、数式が返す空文字 "" も“存在”として拾える（必要に応じて xlValues と使い分け）

' GetDataAreaByFind
' Returns the minimal data rectangle (values/formulas only) as 2D array.
' 値/数式が存在する最小矩形だけを2次元配列で返す（書式の膨張を無視）

Public Function GetDataAreaByFind(ByVal ws As Worksheet) As Variant
	Dim lastCell As Range
	Dim lastRow As Long, lastCol As Long
	Dim firstCell As Range
	Dim dataRange As Range
	
	' Find any used cell by formulas (数式ベースで実データ探索)
	Set lastCell = ws.Cells.Find(What:="*", LookIn:=xlFormulas, _
		LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
	If lastCell Is Nothing Then
		' シートが空（完全にデータ無し）の場合は空配列を返す
		GetDataAreaByFind = VBA.Array()
		Exit Function
	End If
	lastRow = lastCell.Row
	
	Set lastCell = ws.Cells.Find(What:="*", LookIn:=xlFormulas, _
		LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
	lastCol = lastCell.Column
	
	Set firstCell = ws.Cells(1, 1)
	Set dataRange = ws.Range(firstCell, ws.Cells(lastRow, lastCol))
	GetDataAreaByFind = dataRange.Value
End Function

Notes（注意） / 運用のコツ

• 数式が "" を返すセルも “存在” と見なしたい：LookIn:=xlFormulas
• 表示上の値が大事（計算結果が空は無視したい）：LookIn:=xlValues に切替える

UsedRange 膨張のクリーンアップ手順（実務テンプレ）

' ShrinkUsedRange
' Cleans the bloated tail area and nudges Excel to recompute UsedRange.
' 膨張した末尾領域を掃除し、UsedRange の再計算を促す

Public Sub ShrinkUsedRange(ByVal ws As Worksheet)
	Dim last As Range, tail As Range
	Dim lastDataRow As Long, lastDataCol As Long
	Dim maxRow As Long, maxCol As Long
	
	maxRow = ws.Rows.Count
	maxCol = ws.Columns.Count
	
	' 実データの最終セル（Findベース）
	Set last = ws.Cells.Find(What:="*", LookIn:=xlFormulas, _
		LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
	If last Is Nothing Then
		' 完全空なら UsedRange に触れて保存でOK
		ws.UsedRange
		Exit Sub
	End If
	lastDataRow = last.Row
	Set last = ws.Cells.Find(What:="*", LookIn:=xlFormulas, _
		LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious)
	lastDataCol = last.Column
	
	' 余分な行を丸ごとクリア（値＋書式）
	If lastDataRow < maxRow Then
		Set tail = ws.Range(ws.Rows(lastDataRow + 1), ws.Rows(maxRow))
		tail.Clear
	End If
	' 余分な列を丸ごとクリア
	If lastDataCol < maxCol Then
		Set tail = ws.Range(ws.Columns(lastDataCol + 1), ws.Columns(maxCol))
		tail.Clear
	End If
	
	' UsedRange に触れて再評価を促す → 保存すると確度が上がる
	ws.UsedRange
End Sub

Memo：空セルの扱い（Empty vs Nothing）

• Empty（空値）：IsEmpty(v) が True。VarType(v)=vbEmpty
• Nothing（オブジェクト無し）：Set obj = Nothing の世界。値ではない
• データ走査では IsEmpty / LenB(CStr(...)) 等を使う

空白を含まない場合は、CurrentRegion

テーブルの場合