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

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

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

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

前回の記事はこちら。

【初心者向けエクセルVBA】セル範囲を一気にまとめてコピーする方法

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

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

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

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

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

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

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

前回のおさらい
シートのデータ範囲をUsedRangeプロパティで取得する
1. UsedRangeプロパティの注意点
可変の請求データを請求書ひな形に貼り付ける
まとめ
連載目次：データ一覧から請求書を自動で作る

前回のおさらい

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

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

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

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

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

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

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

UsedRangeプロパティです。

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

Worksheetオブジェクト.UsedRange

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

便利ですね～。

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

? wsData.UsedRange.Address

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

UsedRangeプロパティの注意点

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

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

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

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

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

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

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

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

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

まとめ

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

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