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


frame

photo credit: hiphopmilk In the Frame via photopin (license)

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

エクセルVBAで請求データ一覧から請求書を作る初心者向けのシリーズです。

前回の記事はこちら。

【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法
エクセルVBAで請求データ一覧から請求書を自動で作成するシリーズ、まだまだ続きます。今回は、エクセルVBAでセル範囲を一気にまとめてコピーするCopyメソッドの使い方についてお伝えします。

特定のセル範囲をまとめてコピーするCopyメソッドの使い方についてお伝えしました。

ただ、前回お伝えしたのは固定のセル範囲をの決め打ち前提でした。

セル範囲が変更しても対応できる方法をお伝えします。

ということで、エクセルVBAでワークシートのデータのある範囲を取得する方法です。

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

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

スポンサーリンク

前回のおさらい

まずは、前回のおさらいからです。

以下のようなプログラムを作成しました。

Sub 請求書作成()

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

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

wsData.Range("A2:C7").Copy wsTemplate.Range("A21")

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

End Sub

以下の「請求データ」シートの請求データを

エクセルでコピー元のフォントを変更した

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

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

ただ、前述のプログラムだと、8行目にあるように「A2:C7」セルが決め打ちになっていて、コピー元のセル範囲が変更になった時に、プログラムを修正する必要があります。

今回は、そのような修正が必要ないように、「請求データ」シートのデータ範囲をピッタリ取得してくる方法についてお伝えします。

シートのデータ範囲をUsedRangeプロパティで取得する

エクセルVBAでは、ワークシートで使用しているデータの範囲をちょうど取得するための便利なプロパティが良されています。

UsedRangeプロパティです。

書き方は以下の通りです。

Worksheetオブジェクト.UsedRange

これで、対象のワークシートのうち、使用しているセル範囲のみをRangeオブジェクトとして取得することができます。

便利ですね~。

例えば、イミディエイトウィンドウで以下のように入力してみましょう。

? wsData.UsedRange.Address

すると、イミディエイトウィンドウには以下のように出力されます。

エクセルVBAでイミディエイトウィンドウにUsedRangeで取得したセル範囲のアドレスを出力

UsedRangeプロパティの注意点

なお、UsedRangeプロパティで、「使用されている範囲」というのは、厳密にいうと「使用されている最初のセルから最後のセルの範囲」のことをいいます。

例えば、1行目が空行の場合、つまり、最初のセルが2行目の場合は、1行目はUsedRangeで取得できるセル範囲には含まれません。

その点、注意してくださいね。

可変の請求データを請求書ひな形に貼り付ける

UsedRangeプロパティを使う場合、前述のプログラムの8行目を以下のように変更します。

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

見出し行、金額列もUsedRangeで取得されるセル範囲に含まれますので、それも含めてごそッとコピペしちゃいます。

内容も書式も一緒だからOKです。

実行すると、以下のように無事にコピペをすることができます。

エクセルVBAでUsedRangeプロパティを使ってコピペ

まとめ

以上、エクセルVBAでワークシートのデータのある範囲を上手に取得する方法についてお伝えしました。

ソースコードはこちらです。

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プロパティワークシートで使用している範囲を取得する便利なプロパティですので是非覚えておいてください。

次回は、もう少し発展させて、見出し行と金額列を除いてコピペする方法をお伝えします。

【初心者向けエクセル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をコピーしました