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


row

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動画と連動していますので、合わせてご覧いただければと思います。

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

スポンサーリンク

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

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

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

請求データに納品日の列が追加された

この中から、納品日の年月が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プロパティで2つのセルを指定する方法

一つの方法として、先頭のセルと末尾のセルの2点を指定した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プロパティで2つのセルを指定する方法、Offsetプロパティを使う方法など、選択肢がいくつもあります。

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

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

【初心者向けエクセルVBA】セル範囲のクリア~ClearContentsメソッドとClearメソッド
初心者向けエクセルVBAシリーズとして、請求書を作成するマクロの作り方をお伝えしています。今回は、エクセルVBAでセル範囲の値をクリアするClearContentsメソッドとClearメソッドの使い方です。

どうぞお楽しみに!

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

お仕事において特定のデータ一覧から必要な情報を抽出するということは頻繁にありうると思います。ここではデータ一覧から請求書を作るということを目標に、実務で使えるスキルをまっすぐに身に着けることを目的としています。
  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をコピーしました