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

offset

photo credit: bobzee666 Balcony via photopin (license)

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

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

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

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

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

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

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

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

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

スポンサーリンク

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

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

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から先のセル範囲にコピペするというものです。

エクセルVBAでセル範囲をまとめてコピーした

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

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

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

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

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

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

書き方はこちらです。

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

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

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

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

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

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

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

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

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

書き方はこうです。

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

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

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

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

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

エクセルVBAでセル範囲をリサイズする

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

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

これらを用いて、冒頭で紹介したプロシージャの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文による分岐を使って、特定の年月のデータだけをコピペする方法をお伝えします。

【初心者向けエクセルVBA】日付データから年・月・日を取り出す
初心者向けエクセルVBAのシリーズということで請求書を作成するマクロについてお伝えしています。 ということで、今回はエクセルVBAで日付データから年、月、日をそれぞれ取り出す関数を紹介します。

どうぞお楽しみに!

連載目次:データ一覧から請求書を自動で作る

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。
  1. 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
  2. 【初心者向けエクセルVBA】ワークシートをオブジェクト名で取り扱う方法
  3. 【初心者向けエクセルVBA】For~Next文で簡潔にプログラムを書く
  4. 【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
  5. 【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更
  6. 【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
  7. 【初心者向けエクセルVBA】ワークシートのデータのある範囲だけをピッタリ取得する方法
  8. 【初心者向けエクセルVBA】セル範囲の平行移動をする方法・リサイズをする方法
  9. 【初心者向けエクセルVBA】日付データから年・月・日を取り出す
  10. 【初心者向けエクセルVBA】If~Thenを使った条件分岐の超入門
  11. 【初心者向けエクセルVBA】For~Next文でセル範囲を一行ずつ移動させる
  12. 【初心者向けエクセルVBA】セル範囲のクリア~ClearContentsメソッドとClearメソッド
  13. 【初心者向けエクセルVBA】ワークシート・セルを選択する方法の色々について
  14. 【初心者向けエクセルVBA】入力ダイアログを表示するInputBoxメソッドの使い方
  15. 【初心者向けエクセルVBA】日付データから月末日と翌月末日を自動算出する
  16. 【初心者向けエクセルVBA】ワークシートをコピーする方法とそのシート名を変更する方法
  17. 【初心者向けエクセルVBA】オブジェクトを変数にセットして取り扱う方法
  18. 【初心者向けエクセルVBA】Openメソッドで新たなブックを開く方法
  19. 【初心者向けエクセルVBA】現在マクロを書いているブックのフォルダパスを取得する
  20. 【初心者向けエクセルVBA】開いたブックとそのワークシートをオブジェクト変数にセットする
  21. 【初心者向けエクセルVBA】ワークブックを別名で保存して閉じる方法
  22. 【初心者向けエクセルVBA】取引先別に請求書を作成するマクロを作る
タイトルとURLをコピーしました