過去の記事でも触れていますが、Excelのマクロを使う際に、特定のセルを中心とした範囲全体を取得するときには「currentregion」を使うと実行可能です。
今回はcurrentregionで範囲を取得した後に、横1列分だけ少ない範囲を取得する機会があったので備忘記録代わりに記事にしておこうと思います。
ひと固まりの範囲はcurrentregionで取得できる
currentregionの説明はさきほどの過去記事を参考にしていただくとよいと思うので
ここではざっくりの説明にとどめておくことにします。
こんな感じでB2セルを起点としてデータの固まりがあるとします↓
この状態でデータの範囲をExcelマクロで取得する場合には下記のようにします。
Sub test()
Range("b2").CurrentRegion.Select
End Sub
このマクロを実行すると下記の画像のようにB2セルの周辺のひとかたまりのデータが選択状態になります↓
これがcurrentregionの動きになります。
currentregionとcolumns.countで選択した範囲の横の列数を取得する
このcurrentregionで取得した範囲について、データが「横」に何列分あるのか、データが「縦」に何列分あるのかを把握できていると後々便利になることが多いです。
で、currentregionで取得した範囲について、データが「横」に何列分あるのかを調べるには「CurrentRegion.Columns.Count」とすることで調べることができます。
たとえば下記のようにマクロを記載するとcurrentregionで取得した範囲について、データが「横」に何列分あるのかをメッセージボックスに表示することができます。
Sub test()
Range("b2").CurrentRegion.Select
'currentregionで取得した範囲について、データが「横」に何列分あるのかを調べる
MsgBox Range("b2").CurrentRegion.Columns.Count
End Sub
マクロを実行すると下記のようになります↓
これはB2セルの周辺のデータの固まりについて、横にデータが何列分あるかの結果を表示するので、今回の場合には横には６列分のデータがあることが確認できます。
実際に目視で確認してみても確かに6列データがあることが確認できます。
では次に、データの数が増えたり・減ったりしたとしても問題なく横にデータが何列分あるのかを表示してくれるのかを確認するために、データ範囲を変更して再度マクロを実行してみることにします。
今回はデータを削除して6列のデータを３列のデータにしました↓
この状態で先ほどのマクロを実行してみます。
Sub test()
Range("b2").CurrentRegion.Select
'currentregionで取得した範囲について、データが「横」に何列分あるのかを調べる
MsgBox Range("b2").CurrentRegion.Columns.Count
End Sub
実行後の結果は下記のようになりました↓
横のデータは３列分ありますので、きちんと横に何列分のデータがあるのかを取得できていることが確認できました。
columns.count-1やー2で選択した範囲から横1列少ない範囲などを取得する
前置きが長くなりましたが、今回の本題である、特定のセル周辺のひと固まりの範囲から横1列や横2列だけ少ない範囲を取得するにはどういったマクロを記載すればよいのか?についてですが、
ここまでの「CurrentRegion.Columns.Count」と「Resize」を組み合わせることで実現することができます。
下記のようにデータがある場合で
全体のデータのうち横1列分だけ少なく選択する場合にはこういったコードになります↓
Sub test()
Range("b2").CurrentRegion.Select
'■B2セル周辺のデータひとかたまりのうち、横1列だけ少なく選択する
Range("b2").CurrentRegion.Resize(, Range("b2").CurrentRegion.Columns.Count - 1).Select
End Sub
実行するとこのようになります↓
このままでもマクロは動きますが、コードが少し長ったらしいというか、見づらくなるので変数を使って記載すると少しスッキリすると思います。
例えばこんな感じで
Sub test()
'■変数宣言
Dim i As Long
i = Range("b2").CurrentRegion.Columns.Count
'■B2セル周辺のデータひとかたまりのうち、横1列だけ少なく選択する
Range("b2").CurrentRegion.Resize(, i - 1).Select
End Sub
「Range(“b2”).CurrentRegion.Columns.Count」の部分を変数「i」にいれこんであげてマクロを書くとすっきりするかなと思います。
これをベースにして、横2列少なく取得したい場合にはマクロの一部を下記のように書き換えるとOKです。
Sub test()
'■変数宣言
Dim i As Long
i = Range("b2").CurrentRegion.Columns.Count
'■B2セル周辺のデータひとかたまりのうち、横1列だけ少なく選択する
Range("b2").CurrentRegion.Resize(, i - 2).Select
End Sub
マクロを実行すると下記のように横2列少なく選択することができます↓
まとめ
特定のセルの周辺にあるひと固まりの範囲からcurrentregionとresizeを使用して横1列や横2列だけ少ない範囲を取得するマクロについて記事にしました。
このマクロの個人的な使いどころですが、ExcelでSUMなどの数式が特定の列に組み込まれている場合で数式が組み込まれたセル範囲を除いてデータを消去したい場合によく使っています。
何か参考になることがあれば幸いです。
【本日の近況報告】
trelloのメール通知の設定を実施。
タスクが指定した場所などに移動した際に通知や、メールが来るように設定しました。
【本日の１曲】
Ramones/Rockaway Beach
アメリカの初期パンクの代表。ラモーンズ。
ラモーンズの残した名曲・お気に入りの曲はたくさんありますが、夏も近づいてきているので個人的にはこの1曲をチョイス。
最近は外でセミが鳴く声がちらほらと聞こえ出しています。夏が確実に近づいてきています。
