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

block

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

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

前回はコチラの記事。

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

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

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

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

スポンサーリンク

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

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

エクセルの請求データをブロック分け

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

図で示した通り

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

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

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

日付を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

アルゴリズムの流れ

アルゴリズムのおおざっぱな流れとしては

  1. 2行目からから一行ずつ移動していく
  2. 「年月」または「取引先」のどちらかが異なる場合がやってきたら
  3. その前の行までが同じ「年月」かつ同じ「取引先」、つまり同じブロックになる

という流れになりますね。

1の繰り返し処理は11~22行目のDo While~Loopと、13行目のi=i+1で実現をしています。これでA列のセルが空欄になるまで請求データを「なめていく」という動きをします。

2の判定をしているのが14,15行目で、「取引先」はそのままセル内の値を比較すればOKですが、「年月」の比較については前述のfirstDay関数を活用しています。

最終的には範囲を求めたいわけですから、範囲の開始行と最終行が必要になります。

  • 開始行:startRowで保存しておく
  • 最終行:今いる行の前の行

17行目はそれをイミディエイトウィンドウに出力する文ですね。

実行結果

このプログラムを実行すると、イミディエイトウィンドウに以下のように出力されます。

エクセルVBAのイミディエイトウィンドウに出力された範囲情報

この開始行、最終行の値を使って範囲を指定してあげれば、範囲のコピー&ペーストができるようになりそうですね。

まとめ

ちょっとややこしかったかもしれませんが、いかがだったでしょうか?

請求データを2行目から最後まで1回操作するだけで、コピペすべき4つのブロックを見つけることができました。

エクセルVBAは行単位、列単位、セル単位で1つずつ処理をするのがアルゴリズムとしては組みやすいですが、それだけ計算回数や描画回数が増えるためプログラムとしては処理が重くなっていく傾向にあります。

脱初心者としては、範囲つまりRangeオブジェクトの単位でゴソっと処理ができるように作っていくというのが望ましいですね。

次回、実際に請求書にコピペ処理をしていきつつ、請求書作成プログラムを作り込んでいきます。

【脱エクセルVBA初心者】Copyメソッドで範囲まるごとを一発でコピペする
脱エクセルVBA初心者向け、請求書を自動で作る際の様々なテクニックについての連載です。。今回は、範囲単位でまるごとコピペをする方法についてお伝えしつつ、請求書作成プログラムを作り込んでいきます。

どうぞお楽しみに!

連載目次:【脱エクセルVBA初心者】請求書を自動で作る際のテクニック集

「請求書を自動で作る」というニーズはとても多いですから、その際に便利な機能やテクニックをシリーズにてお伝えしていきます。脱エクセルVBA初心者をターゲットにしています。
  1. 【脱エクセルVBA初心者】新規のワークブックを作成、シートのコピーと名前の変更
  2. 【脱エクセルVBA初心者】確認メッセージを表示させずにワークシートを削除する方法
  3. 【脱エクセルVBA初心者】PDFでの出力とファイル名の指定保存そして閉じる
  4. 【脱エクセルVBA初心者】データが含まれる範囲全体を一発で取得してSortで並び替え
  5. 【脱エクセルVBA初心者】コピペはRangeが効率的なのでその範囲を見つけるプログラム
  6. 【脱エクセルVBA初心者】Copyメソッドで範囲まるごとを一発でコピペする
  7. 【脱エクセルVBA初心者】Vlookupで出てしまうエラーをいい感じに回避する方法
  8. 【脱エクセルVBA初心者】請求書作成マクロの全体おさらいと総仕上げ
タイトルとURLをコピーしました