【脱エクセルVBA初心者】コピペはRangeが効率的なのでその範囲を見つけるプログラム

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

脱エクセルVBA初心者向け、請求書を自動で作る際の様々なテクニックについてシリーズでお伝えしています。

前回はコチラの記事。

【脱エクセルVBA初心者】データが含まれる範囲全体を一発で取得してSortで並び替え

脱エクセルVBA初心者向けのテクニックとして、CurrentRegionプロパティでデータ範囲を塊で取得して、それをSortメソッドで並び替えをする方法についてお伝えしていきます。

請求データをSortメソッドを使って、「納品日」「取引先名」をキーに並び替えをする方法についてお伝えしました。

実際に作成する請求書は納品月と取引先名の組み合わせの数だけファイルを作成をすればよくて、それぞれ転記すべき範囲は並び替えによって塊になっています。

では、その「塊」を効率よく見つける方法について解説をしていきます。

検証用の請求データを準備
日付を1日に揃える関数
「年月」と「取引先」の組み合わせの範囲を調べるプログラム
1. アルゴリズムの流れ
2. 実行結果
まとめ
連載目次：【脱エクセルVBA初心者】請求書を自動で作る際のテクニック集

検証用の請求データを準備

データとして以下のような請求データを用意しました。

納品日は色々とありますね。取引先は２社です。

図で示した通り

2016/06のABC株式会社
2016/06の株式会社ホゲホゲ
2015/06の株式会社ホゲホゲ
2016/07の株式会社ホゲホゲ

この４ブロックについて、それぞれ別の請求書を作成するイメージですね。

では、このそれぞれのブロックをどのように見つけていけば良いでしょうか？

日付を1日に揃える関数

まず、納品日に関しては「年」「月」がマッチしていれば「日」が異なっていても同じ請求書に入れ込んで良いわけです。

「日」を考慮しなくて良いように、とある関数を作ってしまいます。

こちらです。

Function firstDay(ByVal d As Date) As Date

firstDay = DateSerial(Year(d), Month(d), 1)

End Function

firstDayという関数ですが、日付型の引数を一つ受け取って、その年、月はそのままで日だけを1日そろえた日付を返すという関数です。

DateSerial関数は年、月、日をそれぞれ指定して日付を生成する関数ですね。

DateSerial(年,月,日)

納品日をこの関数firstDayにぶち込んで1日に揃えてしまうことで、結果的として年月がマッチしているかどうかを判定するのに役立ちます。

「年月」と「取引先」の組み合わせの範囲を調べるプログラム

このfirstDayを使って、「年月と取引先の組み合わせの範囲」を抽出するプログラムを作ってみましょう。

こちらです。

Sub wsDataRangeFind()

Dim wsData As Worksheet '「請求データ」シート
Set wsData = ThisWorkbook.Worksheets("請求データ")

Dim startRow As Long 'コピー範囲の最初の行と最終行を格納
startRow = 2

Dim i As Long
i = 2
Do While wsData.Cells(i, 1).Value <> ""

    i = i + 1
    If firstDay(wsData.Cells(i, 1).Value) <> firstDay(wsData.Cells(startRow, 1).Value) Or _
        wsData.Cells(i, 2).Value <> wsData.Cells(startRow, 2).Value Then

        Debug.Print "Start:" & startRow & ",End:" & i - 1
        startRow = i

    End If

Loop

End Sub