【初心者向けエクセルVBA】行の数をカウントする＆不要な行を隠す

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

請求データ一覧から請求書を自動で作成するシリーズのVBA講座をシリーズでお伝えしています。

前回の以下記事までで、請求データから請求書ひな形にデータを転記するという処理まで実現しました。

【初心者向けエクセルVBA】For～Next文で簡潔にプログラムを書く

エクセルVBAではコピー＆ペーストを自動化するという処理がよく活用されますが、その際に活用する繰り返し処理用の命令であるFor～Next文を徹底的に解説し、請求書の自動作成プログラムを簡略化していきます。

tonari-it.com

2015.05.14

今回はこのVBAプログラムをさらにブラッシュアップします。

行数をカウントする、行を隠すなどのテクニックを駆使して、品目の数に変化があっても請求書がきちんと作れるようにしてみます。

いずれも請求書に限らず、データや帳票を扱ったエクセルVBAではかなり重宝するテクニックですので、非常にお勧めのテクニックです。

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

ではよろしくお願いします！

前回のおさらい：品目数が増えたらどうなるの…？

「請求データ」シートから「請求書ひな形」にデータを転記するというプログラムをFor～Next文を使って作りました。

Sub 請求書作成()

Dim i As Long, j As Long, k As Long
k = 21
For i = 2 To 4
    For j = 1 To 3
        wsTemplate.Cells(k, j).Value = wsData.Cells(i, j).Value
    Next j
    k = k + 1
Next i

End Sub

ただこちらのプログラムだと、品目の数が”3″で固定されちゃってます。

品目の数が”3″以外のときは、For～Nextのカウント変数iの範囲を都度変更しなければいけません。

これは面倒でしたね…てへ。

今回はそれを解消していきます。

最終の行数がいくつなのか調べる

さて、この問題を解消するためには、VBAを実行したときに「請求データのデータの数がいくつか？」ということを都度わかるようにすれば良さそうですよね。

そのような場合は、

これで最終行数のカウントを整数で取得することができます。

…なんだか難しいぞ、と思われるかも知れませんが、この一文をコピペしてWorksheetオブジェクトと列数だけ変更してもらえれば良いので、細かいことは後回しでOKですよ。コピペでいいっす。

「列数」についてですが、ここで指定した列についての最終行を調べることになります。

今回の場合はどの列を指定してもいいのですが、わかりやすく”1″を設定します。

従いまして実際には

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

と書いてあげればOKですね。

ちなみに、以下で列数も取得できますので、合わせてお伝えしておきますね。

なお、こちらの方法は最終行や最終列が非表示になっているときにはうまく動作しませんので、ご注意くださいね。

For～Nextの最終値は…？

これにて行数が判明しましたので、次にFor～Nextの最終値を決めていきます。

例えばデータ一覧が

ですと、For～Next文は2行目から7行目までを繰り返してほしい、ということになりますね。ですから、変数rowsDataには1列目の最終データがある行、つまり”7″が入ります。

Dim i As Long, j As Long, k As Long
k = 21
For i = 2 To rowsData
    For j = 1 To 3
        wsTemplate.Cells(k, j).Value = wsData.Cells(i, j).Value
    Next j
    k = k + 1
Next i

これで良さそうです。

貼り付け先の行数も可変にしないといけない

では実行してみましょう！

…ってあれ？

データを転記する先の「請求書ひな形」シート側が5行分しか用意していなかったものですから、1行はみ出てしまいました。

これも解消しないといけませんね。

貼り付け先の行数をとても多くする

まず請求書ひな形のデータ転記する領域をたくさん増やしちゃいましょう。

はい、長いですね。

こちらの例では品目数を30まで増やしました。別に50でも100でもいいので、毎月の請求書で最も品目数が多いときでも対応できる数にしておきましょう。

計算式もきちんとコピーしつつ、小計のSUM式も合わせておくのを忘れないようにしてくださいね。

これでVBAを実行してもはみ出てしまうことはありません。

Hiddenプロパティで不要な行を隠す

あとは不要な行を見えなくすればOKなのですが、実はこれもVBAでプログラムすることができます。

まず使うのが、特定の行範囲をRangeオブジェクトとして取得するRowsプロパティです。

これで、隠す行範囲をRangeオブジェクトとして取得します。

それで、そのRangeオブジェクトを隠すのがHiddenプロパティです。

Trueに設定すれば非表示に、Falseを設定すれば表示となります。

これで特定の行範囲を隠すことができます。

開始行と最終行の求め方

品目数が”6″だとすると27行目が開始行になるのですが、品目数は”可変”ですのでrowsDataを使って決めます。

品目数は”rowsData-1″ですので、データ転記の最初の行21から品目数分を足したところから隠せば良いということになります。したがって「21+rowsData-1」となります。

今回の例ですと、最後の行は50にしていますが、これは毎回固定の50でOKですね。

従いまして今回の例では

wsTemplate.Rows(21 + rowsData - 1 & ":50").Hidden = True 'データがない行を隠す

と書きます。

“開始行:最終行”は文字列で指定する必要がありますので、「21+rowsData-1」と「:50」を&で連結するという書き方をしています。

これについては、後日の記事で詳しく解説しますので、今は深く考えずにそのまま記述していただいてOKです。

ではこちらで実行をしてみましょう。

はい、ばっちり不要な行が隠されていますね。

気持ちいいですね～。

非表示の行範囲を再表示する

ちなみにですが、隠れた行を再表示するのは

wsTemplate.Rows(21 + rowsData - 1 & ":50").Hidden = False '隠れているかもしれない行を再表示しておく

と書けばOKです。

このエクセルファイルを繰り返して使うのであれば、前回の作業で行が隠されているかも知れませんので、請求データから請求書ひな形へ転記する前に、上記の処理を入れておくと安心です。

まとめ

以上、エクセルVBAで行の数をカウントする＆不要な行を隠す方法をお伝えしました。

これで、請求データのデータ数が可変だった場合でも請求書を作れるようになりました。

いずれも請求書に限らずいろいろな場所で活躍するテクニックですので、ぜひマスターして頂ければと思います。

今回の完成したプログラムはこちらです。

Sub 請求書作成()

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

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
    For j = 1 To 3
        wsTemplate.Cells(k, j).Value = wsData.Cells(i, j).Value
    Next j
    k = k + 1
Next i

wsTemplate.Rows(21 + rowsData - 1 & ":50").Hidden = True 'データがない行を隠す

End Sub

次回、文字列に関するいくつかのテクニックを習得しつつ、請求書を自動で作るシリーズの序盤の総仕上げをしています。

【初心者向けエクセルVBA】文字列の連結＆Format関数での書式変更

エクセルVBAで文字列を連結する、文字列の書式を変更するなどの「文字列」に関するいくつかのテクニックを紹介しつつ、データ一覧から請求書を自動で作成するというシリーズ連載の序盤の完成を目指します。

tonari-it.com

2015.05.21

どうぞお楽しみに！

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

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

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】取引先別に請求書を作成するマクロを作る