【初心者向けエクセルVBA】For～Next文でセル範囲を一行ずつ移動させる

photo credit: mikecogh Sawtooth Row via photopin (license)

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

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

前回の記事はこちら！

【初心者向けエクセルVBA】If～Thenを使った条件分岐の超入門

請求データ一覧から請求書を自動で作成するシリーズのVBA講座です。今回は間違いなくお世話になる条件分岐If～Thenの使い方の超基本をマスターしていき特定のデータのみ転記するように改良していきます。

エクセルVBAで条件分岐処理を行うIf文の使い方についてお伝えしました。

さて、エクセルVBAでコピペする場合、セルをひとつひとつコピペするよりも、セル範囲でコピペした方が実行速度も速くなりますし、すっきりとしたコードで書くことができます。

ただ、For～Next文を使って、行単位で移動させながらコピペしたいときがありますよね。

ということで、今回はエクセルVBAでFor～Next文でセル範囲を一行ずつ移動させる方法をお伝えします。

なお、本記事は以下のYouTube動画と連動していますので、合わせてご覧いただければと思います。

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

前回のおさらい：セル範囲を一行ずつ移動させながらコピペしたい
変数を使ってセル範囲を指定する
1. Rangeプロパティで２つのセルを指定する方法
2. Offsetプロパティを使う方法
まとめ
連載目次：データ一覧から請求書を自動で作る

前回のおさらい：セル範囲を一行ずつ移動させながらコピペしたい

まず、今回のお題から紹介します。

以下のような請求データがあります。

この中から、納品日の年月が2018年1月のもののみを抽出して、別のシートに転記をしたいというのがやりたいことです。

また前回作成したプログラムはこちらです。

Sub 請求書作成()

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

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

Dim i As Long, j As Long, k As Long
k = 21
For i = 2 To rowsData
    Dim deliDate As Date
    deliDate = wsData.Cells(i, 1).Value
    If Year(deliDate) = 2018 And Month(deliDate) = 1 Then
        For j = 1 To 3
            wsTemplate.Cells(k, j).Value = wsData.Cells(i, j + 1).Value
        Next j
        k = k + 1
    End If
Next i

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

一応、目的は達成できていますが、14～16行目のFor～Next文を見てみると、列方向へ移動しながらセル一つ一つをコピペするような内容になっていますよね。

以下の記事でもお伝えした通り、セルの転記は一つ一つではなく、まとめて行ったほうが処理速度やコードの見やすさの点で良いことが多いのです。

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

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

ですから、今回はその列方向のFor～Nextをせずに行単位でセル範囲を移動しながら、まとめて転記をする方法を考えていきたいと思います。

変数を使ってセル範囲を指定する

セル範囲のコピーであれば、対象のセル範囲を「wsData.Range(“B2:D2”)」というようにアドレスで表現する方法が真っ先に思いつきます。

ですが、今回は「納品日が2018年1月なのかどうか」を1行ずつFor～Next文で動かしながら判定をしていく必要があります。ですから、アドレスでの指定ではそれが叶いません。

つまり、「カウント用変数を使ったセル範囲の指定」の仕方を知る必要があります。

Rangeプロパティで２つのセルを指定する方法

一つの方法として、先頭のセルと末尾のセルの２点を指定したRangeプロパティによる方法があります。

こちらです。

Worksheetオブジェクト.Range(先頭のセル, 末尾のセル)

先頭のセルはつまり取得したいセル範囲の左上のセル、末尾のセルは右下のセルで、それぞれRangeオブジェクトを指定します。

例えば、「請求データ」シートの「B2:D2」のセル範囲であれば、以下のように書けば取得できます。

wsData.Range(wsData.Cells(2, 2), wsData.Cells(2, 4))

ですから、これをFor～Next文内でカウント用変数を使って表現すると、冒頭のプログラムの15～17行目は以下のように書き換えることができます。

wsData.Range(wsData.Cells(i, 2), wsData.Cells(i, 4)).Copy wsTemplate.Cells(k, 1)

そこそこシンプルになりましたよね。あと、コピペの回数も減りました。

Offsetプロパティを使う方法

もうひとつ、別の方法としてOffsetプロパティを使う方法があります。

Offsetプロパティは行数と列数を整数で指定することで、元のセル範囲を平行移動した位置のセル範囲を取得することができます。

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

ですから、例えば「B1:D1」から1行プラスしたセル範囲であれば、以下のようにして取得できます。

wsData.Range("B1:D1").Offset(1, 0)

この方法を使うと、冒頭のプログラムの15～17行目は以下のようになります。

wsData.Range("B1:D1").Offset(i - 1, 0).Copy wsTemplate.Cells(k, 1)

Offsetプロパティの詳細は以下記事ご覧くださいね。

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

初心者向けエクセルVBA のシリーズで、請求データ一覧から請求書を作成する方法についてお伝えしています。今回は、エクセルVBAでセル範囲を並行移動する方法とセル範囲をリサイズする方法です。

まとめ

以上、エクセルVBAでFor～Next文内でセル範囲を一行ずつ移動させる方法について解説しました。

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

Sub 請求書作成()

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

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

Dim i As Long, k As Long
k = 21
For i = 2 To rowsData
    Dim deliDate As Date
    deliDate = wsData.Cells(i, 1).Value
    If Year(deliDate) = 2018 And Month(deliDate) = 1 Then
        wsData.Range(wsData.Cells(i, 2), wsData.Cells(i, 4)).Copy wsTemplate.Cells(k, 1)
        k = k + 1
    End If
Next i

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

End Sub

セル一つ一つやってもいいのですが、できるだけセルの読み書きの回数は減らしたほうが良いプログラムになります。

また、解決方法もRangeプロパティで２つのセルを指定する方法、Offsetプロパティを使う方法など、選択肢がいくつもあります。

いつも「もっといい方法あるかな？」と考えながら作成をしていきたいですよね。

次回は、初期処理として、転記先のセル範囲をクリアする方法についてお伝えします。