Excel VBA セル位置が変わっても特定の範囲を選択する方法 | Find&Address
仕事でVBAを使うことがあり、やりたいことはネットで調べていますが、VBA初心者の私が分かりづらかったものを、初心者でも分かるように説明したいと思います。
効率的なコードが書けていない可能性もありますので、予めご了承ください。
今回はFindとAddressを使用し、セル位置が変わっても特定の範囲を選択し、コピー等を行う方法を紹介します。
使用ケース
例として、以下のような上部には従業員別の数値等の一覧、下部に合計が集計（赤枠で囲った範囲）された表があるとします。
従業員の変動があると列が増えたり減ったりするため、合計の位置（列）も変わりますが、変動があっても合計の数値部分のみVBAで選択しコピーしたい、というケースを想定します。
かなりニッチで、そんなケースあるんかい！という感じですが、私はありました・・・。
サンプルコード
Sub cellsearch()
Dim c
Dim c2
Set c = ThisWorkbook.Sheets("従業員一覧").Range("B:B").Find("合計", SearchOrder:=xlByColumns)
c2 = c.Cells(2, 2).Address
Range(c2).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Copy
Sheets("集約").Range("c3").PasteSpecial Paste:=xlPasteValues
End Sub
どういう動きをしているかというと、以下の流れになります（もっといい方法があるかもしれませんが！）。
- ①テキストの「合計」検索（例のB12）
- ②テキストの「合計」の1つ右、1つ下のセル位置を取得
- ③②で取得したセルから最終行・最終列を選択、コピー
- ④指定場所へ貼り付け
コード解説
Set c = ThisWorkbook.Sheets("従業員一覧").Range("B:B").Find("合計", SearchOrder:=xlByColumns)
位置が変わる範囲をどのように指定しているかというと、今回の例であれば「合計」というテキストがあるように、テキスト等の固定されたデータの位置を基準にして、そこから目的のデータがある位置を取得しようと考えました。
なので、まずはテキストの「合計」を検索します。
検索はFindメソッドを使用します。
Find(What, [After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat])
[]内は省略可能
- What：検索するデータを指定します。セル内の文字列などを指定します。
- After：検索を開始するセルを指定します。このセルの次のセルから検索が開始されます。
- LookIn：情報の種類を指定します。（xlFormulas：数式／xlValues：値／xlComents：コメント）
- LookAt：検索テキストの部分一致か、完全一致を指定します。（xlPart：部分一致／xlWhole：完全一致）
- SearchOrder：検索方向を指定します。（xlByColumns：列方向に検索／xlByRows：行方向に検索）
- SearchDirection：検索の向きを指定します。（xlNext：順方向／xlPrevious：逆方向）
- MatchCase：大文字と小文字を区別するか指定します。
- MatchByte：半角と全角を区別するか指定します。
- SearchFormat：書式検索するか指定します。
今回は後の工程で変数としてセットしたいため、予めRangeで行を指定していますが、Findで「合計」というテキストを列方向に検索している流れとなります。
c2 = c.Cells(2, 2).Address
Cellsを使用し、c（テキスト「合計」のセル）の位置より1つ右・1つ下のセルの場所をc2として変数にセットしています。
本来Addressは、現在位置の取得に使用することが多いと思うのですが、今回はCellsと組み合わせ、指定した移動位置の取得に使用しています。
Range(c2).Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Copy
Sheets("集約").Range("c3").PasteSpecial Paste:=xlPasteValues
あとは集約先に貼り付けして完了です。
例では同じファイルの別のシートに貼り付けしていますが、別のファイルへ貼り付けする場合は別途指定ください。
以上、FindとAddressを使用し、セル位置が変わっても特定の範囲を選択し、コピー等を行う方法でした。
簡単でニッチなプログラムになりますが、どなたかの参考になれば幸いです！
新規ブックを作成しマクロが登録されたボタンを設置する方法 | OnAction
複数のワークシートをまとめて表示・非表示にする方法 | Visible Not.Visible
セル位置が変わっても特定の範囲を選択する方法 | Find&Address
電話番号からハイフンを削除し0から表示させる方法 | Evaluate&Replace
ダイアログボックスからファイルを選択して開く | GetOpenFilename
