Excelマクロで範囲を取得するにはcurrentregionとoffsetを組み合わせて使うとだいたいうまくいく。

2021年9月20日

Excelマクロで何か作業を自動化・効率化するときには「範囲を取得する」ケースが割と頻繁におとずれます。

そんなときに自分がよくやる範囲の取得方法を記事にしておきます。

税理士業務の範囲であればcurrentregionとoffsetを使えばだいたい上手くいきます。

Excelマクロでの作業の自動化には範囲の取得をするケースが多い。

今回はExcelマクロで何かデータを加工して会計ソフトへデータをインポートする作業を前提に話を進めていきます。

例えばこんな感じで顧問先が作成した経費データがあるとします↓

このデータを会計ソフトにコピーして、別のシートに張り付けて～etcといった作業をするためにB３セル～E12セルまでの表全体を取得したいとした場合にどうするかというとこうやります↓

Sub get_area()

Range("b3").CurrentRegion.Select

End Sub

これでB３セルとその周辺範囲を取得することができます。

このデータが毎回このB3セルを起点として作成されている場合にはこれで問題ないのですが、たまにデータの起点がずれて作成されているときがあります。

例えばこんな感じでD３セルを起点としてデータが記載されている場合などです。（今回は説明しやすいように大袈裟にずらしましたが）

データがこの状態でさきほどのマクロを実行するとこんな感じになってうまく範囲取得ができません。

マクロを実行するとこうなります↓

もしこういうケースが考えられる場合にはマクロの書き方を少し変更する必要がでてきます。

データがいつもと違う場所に移動しても範囲取得するにはfindで起点をみつけてからcurrentregionで全体の範囲を取得する

データの起点が毎回変わったとしてもきちんと範囲取得するにはひと手間加えます。

具体的には変数宣言をしておき、その後find関数で目印となるセル（データの起点）を検索し変数に格納させてからcurrentregionで範囲を取得します。

Sub get_area2()

’■変数宣言　

Dim rng As Range

’■　変数にデータの起点を検索して格納する。みつからなければマクロ終了する

Set rng = Cells.Find(what:="日付")

if rng is nothing then 

　msgbox"日付というセルはありません"
　exit sub

end if
    
’■処理実行（範囲取得）

rng.CurrentRegion.Select

End Sub

今回は「日付」と入力されたセルを検索してそれを変数「rng」に格納しています。

その後、currentregionとすることで「日付」と記載されたセル周辺の範囲を取得しています。

こうしておくとデータがどの場所にあっても範囲の取得が可能です。

データが下記の図のように

「なんでそこにデータを作った！？　嫌がらせか！？」

って場所にあってもマクロを実行すると上手く範囲を取得することができます↓

マクロを実行すると↓

問題なくデータ範囲の取得が可能です。

そもそも、毎回決まった位置からデータの入力をするようにしないといけないのですが。。。

データのタイトル行以外の範囲を取得したいならoffsetで全体をズラす。

データのタイトル行は取得したくない。つまり、正味のデータ部分だけ取得したい場合にはどうするか。

自分がよくやるのはoffsetで範囲を下にズラすやりかたです。

さきほどのケースでいうと一旦全体の範囲を取得しておいて↓

offsetで下にズラします↓

これでタイトル行以外の全範囲を取得することが可能です。

マクロはこのようになります。最後（20行目のハイライト行で表示している箇所）に「offset(1,0)」を追加するだけでできますので簡単です。

Sub get_area3()

'■変数宣言

Dim rng As Range

'■　変数にデータの起点を検索して格納する。みつからなければマクロ終了する

Set rng = Cells.Find(what:="日付")

If rng Is Nothing Then

  MsgBox "日付というセルはありません"
  Exit Sub

End If
    
'■処理実行（タイトル行以外の範囲取得）

rng.CurrentRegion.Offset(1, 0).Select

End Sub

この範囲の取得の仕方はデータを下にズラしているだけなので

厳密にいうと、空白行も取得していることになるのですが、会計ソフトにインポートするという作業自体に影響がでないのであればこのやり方でいいんじゃないかと思っています。

どうしてもデータの部分だけ取得したいならこんなマクロはいかがでしょうか？

Sub get_area4()

'■変数宣言

Dim rng As Range

'■　変数にデータの起点を検索して格納する。みつからなければマクロ終了する

Set rng = Cells.Find(what:="日付")

If rng Is Nothing Then

  MsgBox "日付というセルはありません"
  Exit Sub

End If
    
'■処理実行（データ部分のみ範囲取得）

With rng.CurrentRegion

    .Offset(1, 0).Resize(.Rows.Count - 1).Select

End With

End Sub