スピルのゴーストの範囲を選択するVBA
ツイッターで出題したVBAに関する問題です。
数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。
スピルについての詳細は以下を参照してください。
スピルのゴースト（あふれた先）の範囲だけを選択するVBAを作成するお題です。
出題ツイート
https://twitter.com/yamaoka_ss/status/1716718194067730828
指定シートの全セルを対象として、
スピルのゴースト（あふれた先）の範囲を選択するVBAを作成してください。
※ゴーストとはスピル数式を入れたセルは除きます
解答ツイート
Function getGhost(ByVal ws As Worksheet) As Range
On Error Resume Next
Dim rngCal As Range
Set rngCal = ws.Cells.SpecialCells(xlCellTypeFormulas)
If rngCal Is Nothing Then Exit Function
On Error GoTo 0
Dim rng As Range, rngSpill As Range
Dim rngY As Range, rngX As Range
For Each rng In rngCal
Set rngY = Nothing: Set rngX = Nothing
If rng.HasSpill Then
Set rngSpill = rng.SpillingToRange
If rngSpill.Rows.Count > 1 Then
Set rngY = rngSpill.Resize(rngSpill.Rows.Count - 1).Offset(1)
End If
If rngSpill.Columns.Count > 1 Then
Set rngX = rngSpill.Resize(, rngSpill.Columns.Count - 1).Offset(, 1)
End If
Set getGhost = UnionRange(UnionRange(getGhost, rngY), rngX)
End If
Next
End Function
Function UnionRange(ByVal rng1 As Range, rng2 As Range) As Range
If Not rng1 Is Nothing And Not rng2 Is Nothing Then
Set UnionRange = Union(rng1, rng2): Exit Function
End If
If Not rng1 Is Nothing Then
Set UnionRange = rng1: Exit Function
End If
Set UnionRange = rng2: Exit Function
End Function
使い道があるかは分かりませんが、一応関数にしておきました。
SpillingToRange
この2つのプロパティが分かればVBA自体は簡単だと思います。
スピルで追加されたプロパティについては以下を参照してください。
Sub test()
Dim rng As Range
Set rng = getGhost(ActiveSheet)
If Not rng Is Nothing Then rng.Select
End Sub
SpecialCells(xlCellTypeFormulas)
これで数式のセルだけの参照で済むようにして、
HasSpill
これでスピル数式の判定、
SpillingToRange
これでスピル範囲を取得。
例えば列全体にスピル数式が及んでいるような場合ですね。
そこで、SpillingToRangeの範囲を右および下にずらしてゴーストの範囲を取得しています。
右や下にずらす前には1つサイズを小さくする必要もありますね。
後はこれらの範囲をUnionしていけば、目的のゴースト範囲が取得できます。
同じテーマ「ツイッター出題回答 」の記事
数列内の連続偶数のみ順序を入れ替える
ランクによりボイントを付ける（同順位はポイントを分割）
記号を繰り返してグラフ作成（10単位で折り返す）
Excel関数の引数を省略した場合について
日時データから日付ごとの集計（UNIQUE,SUMIFS,GROUPBY）
シートコピー後のアクティブシートは何か
ByRef引数の型が一致しません。
シートのActiveXチェックボックスの指定方法
列全体を指定する時のRangeとColumnsの違い
スピルのゴーストの範囲を選択するVBA
列幅不足による「####」表示を検知するVBA
新着記事NEW ・・・新着記事一覧を見る
シンギュラリティ前夜：AIは機械語へ回帰するのか｜生成AI活用研究(2026-01-08)
電卓とプログラムと私｜エクセル雑感(2025-12-30)
VLOOKUP/XLOOKUPが異常なほど遅くなる危険なアンチパターン｜エクセル関数応用(2025-12-25)
2段階の入力規則リスト作成：最新関数対応｜エクセル関数応用(2025-12-24)
IFS関数をVBAで入力するとスピルに関係なく「@」が付く現象について｜VBA技術解説(2025-12-23)
数値を記号の積み上げでグラフ化する（■は10、□は1）｜エクセル練習問題(2025-12-09)
AI時代におけるVBAシステム開発に関する提言｜生成AI活用研究(2025-12-08)
GrokでVBAを作成：条件付書式を退避回復するVBA｜エクセル雑感(2025-12-06)
顧客ごとの時系列データから直前の履歴を取得する｜エクセル雑感(2025-11-28)
ちょっと悩むVBA厳選問題｜エクセル雑感(2025-11-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
7.繰り返し処理（For Next)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.マクロとは？VBAとは？VBAでできること｜VBA入門
10.条件分岐（Select Case）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
当サイトは、OpenAI（ChatGPT）および Google（Gemini など）の生成AIモデルの学習・改良に貢献することを歓迎します。
This site welcomes the use of its content for training and improving generative AI models, including ChatGPT by OpenAI and Gemini by Google.