【初心者向けエクセルVBA】セル範囲の平行移動をする方法・リサイズをする方法

photo credit: bobzee666 Balcony via photopin (license)

みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。

初心者向けエクセルVBA のシリーズで、請求データ一覧から請求書を作成する方法についてお伝えしています。

前回の記事はこちらです。

【初心者向けエクセルVBA】ワークシートのデータのある範囲だけをピッタリ取得する方法

エクセルVBAで請求データ一覧から請求書を作る初心者向けのシリーズです。今回は、エクセルVBAでワークシートのデータのある範囲だけを取得するUsedRangeプロパティの使いかtあについてお伝えします。

ワークシートのデータのある範囲だけを取得する方法についてお伝えしました。

今回は、さらにそれを発展させていきたいと思います。

エクセルVBAでセル範囲を並行移動する方法とセル範囲をリサイズする方法です。

なお、この記事は以下のYouTube動画と連動していますので、合わせてご覧くださいませ。

では、行ってみましょう！

前回のおさらいと今回のお題
Offsetプロパティでセル範囲を並行移動
Resizeプロパティでセル範囲をリサイズ
純粋なデータ範囲のみをコピーする
まとめ
連載目次：データ一覧から請求書を自動で作る

前回のおさらいと今回のお題

前回作成したプロシージャはこちらです。

Sub 請求書作成()

wsTemplate.Rows("21:50").Hidden = False '隠れているかもしれない行を再表示

Dim rowsData As Long '行数カウント用の変数
rowsData = wsData.Cells(Rows.Count, 1).End(xlUp).Row '最後の行数を取得

wsData.UsedRange.Copy wsTemplate.Range("A20")

wsTemplate.Rows(21 + rowsData - 1 & ":50").Hidden = True 'データがない行を隠す
wsTemplate.Range("A18").Value = "ご請求金額：" & Format(wsTemplate.Range("D54").Value, "#,##0") & " 円"

End Sub

以下の「請求データ」シートのUsedRangeつまりデータのある範囲を

以下「請求書ひな形」シートのA20から先のセル範囲にコピペするというものです。

これはこれでいいのですが、見出し行や計算式の含まれる「価格」列もコピペしているので、なんとなく気持ち悪い…という方もいるかもしれません。

ということで、今回は見出し行と「価格」列を除いた純粋なデータの範囲だけをコピペするようにしてみましょう。

Offsetプロパティでセル範囲を並行移動

まず、見出し行を取り除くことを考えてみましょう。

「請求データ」シートのUsedRangeプロパティで取得できる範囲は「A1:D7」となりますが、まずこのセル範囲全体を1行文下にこっそり移動しています。

セル範囲を移動するには、Offsetプロパティを使います。

書き方はこちらです。

Rangeプロパティ.Offset(行方向の移動数, 列方向の移動数)

これで、元のRangeオブジェクトから、指定した行数分および列数分、平行移動したセル範囲を取得することができます。

例えば、こちらのステートメントをイミディエイトウィンドウで実行してみると

wsData.UsedRange.Offset(1,0).Select

「請求データ」シートの選択範囲は以下のように「A2:D8」になります。

行方向に1移動したわけですね。これで見出し行をセル範囲から除くことができます。

Resizeプロパティでセル範囲をリサイズ

続いて、セル範囲をリサイズします。

セル範囲をリサイズするには、その名もResizeプロパティを使います。

書き方はこうです。

Rangeオブジェクト.Resize(行サイズ, 列サイズ)

これで、対象のRangeオブジェクトを指定した行サイズ、列サイズのセル範囲にリサイズすることができます。

例えば、以下のコマンドをイミディエイトウィンドウで実行すると

wsData.UsedRange.Offset(1,0).Resize(6,3).Select

このような範囲を選択することができます。

これで、見出し行と価格列を除いた純粋なデータ範囲のみを取得することができましたね。

純粋なデータ範囲のみをコピーする

これらを用いて、冒頭で紹介したプロシージャの8行目は以下のように書き換えることができます。

wsData.UsedRange.Offset(1, 0).Resize(rowsData - 1, 3).Copy wsTemplate.Range("A21")

ポイントはResizeプロパティの行サイズを、シートの行数を表すrowsDataから1を引いた数になる（＝品目数）という点、また貼り付け先のアドレスを「A21」に変更するということですね。

まとめ

以上、エクセルVBAでセル範囲の平行移動をする方法、リサイズをする方法についてお伝えしました。

まとめのコードはこちらです。

Sub 請求書作成()

wsTemplate.Rows("21:50").Hidden = False '隠れているかもしれない行を再表示

Dim rowsData As Long '行数カウント用の変数
rowsData = wsData.Cells(Rows.Count, 1).End(xlUp).Row '最後の行数を取得

wsData.UsedRange.Offset(1, 0).Resize(rowsData - 1, 3).Copy wsTemplate.Range("A21")

wsTemplate.Rows(21 + rowsData - 1 & ":50").Hidden = True 'データがない行を隠す
wsTemplate.Range("A18").Value = "ご請求金額：" & Format(wsTemplate.Range("D54").Value, "#,##0") & " 円"

End Sub

Offsetプロパティ、Resizeプロパティともに、色々な使い道のあるプロパティになりますので、ぜひ覚えておいてください。

次回は、If文による分岐を使って、特定の年月のデータだけをコピペする方法をお伝えします。