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

Rows

siskaV / Pixabay

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

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

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

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

今回はこの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オブジェクト.Cells(Rows.Count, 列数).End(xlUp).Row

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

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

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

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

従いまして実際には

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

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

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

Worksheetオブジェクト.Cells(行数, Columns.Count). _
 End(xlToLeft).Column

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

For~Nextの最終値は…?

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

例えばデータ一覧が

エクセルVBA用請求データ一覧の品目を増やした

ですと、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

これで良さそうです。

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

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

エクセルVBAで貼り付け先の行数が不足

…ってあれ?

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

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

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

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

請求書ひな形を長く

はい、長いですね。

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

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

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

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

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

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

Worksheetオブジェクト.Rows(“開始行:最終行”)

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

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

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です。

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

エクセルVBAでの行範囲の非表示の実行結果

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

気持ちいいですね~。

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

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

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

どうぞお楽しみに!

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

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

コメント

  1. ハム より:

    初めて書き込みさせていただきます、ハムと申します。
    VBAはその言葉すら最近知ったド素人なのですが、タカハシさんの記事を一つずつ追いながら、ちまちま練習しています。

    2つ、質問があります。

    1つ目は、上記の完成したプログラムの5行目に、「Dim strtotal As String ‘ご請求金額をテキスト化」という記述がありますが、これはどういった意味を持っているのか? といったことです。

    2つ目は、隠れた行を再表示するための「Worksheetオブジェクト.Rows(“開始行:最終行”).Hidden = False」という記述はどこに記載すれば上手く機能してくれるのだろうか? ということです。
    再表示は自分で考えて何箇所か書き込んでみたのですが、エラーになってしまいます。

    おそらく、初心者丸出しの恥ずかしい質問なのだろうと思いますが、時間のあるときにお答えいただけたらと思います。

    こんな理解度ですが、タカハシさんの記事は非常に読みやすく、分かりやすいと感じています。
    今後も、仕事の合間等に少しずつ読み進めたいと思います。
    雑長文失礼しました。

    • ハムさん

      コメントありがとうございます!

      まず

      Dim strtotal As String

      ですが…これは不要ですね。消し忘れだと思いますので、削除致しました。
      ご指摘ありがとうございます。

      また

      Worksheetオブジェクト.Rows(“開始行:最終行”).Hidden = False

      ですが、「まとめ」に記載させて頂いているソースコードであれば14行目あたりに入れて頂くと良いと思います。

      どうぞよろしくお願いいたします!

      • ハム より:

        教えていただいた通りにやったところ、上手くいきました。ありがとうございます。

  2. てつもん より:

    はじめまして。
    昨日からこのサイトをみながらVbeの勉強をさせていただいております。

    お恥ずかしいかぎりなのですが、
    まとめにある26行目のhidden構文のところで構文エラーがでてしまい、なんど見直してもどこがまちがっているのか分からず、躓いております。
     
    debug.print rowsdateをしてみたところ、最終行は取得できているようですし、請求書への記入もできていますが、26行目の構文をいれると、構文エラーとなってしまいます。
    スペルミスなども考えましたが、見つけられませんでした。

    この場合、どのようなところでミスをしている可能性がありますでしょうか?

    お忙しい中、つまらない質問で申し訳ございませんが、どうか宜しくお願いします。

    • てつもん より:

      すみません 本日もういちどやってみたところできました。
      &と”のあいだのスペースが無かったのが問題だったようです。

      お騒がせしてすみませんでした。

      • てつもんさん

        コメントありがとうございます。
        そして返事が遅くて恐縮です…

        無事に動いたということで、ホッとしました。
        けっこうスペース忘れがちですよね…

        引き続きどうぞよろしくお願いいたします!

  3. ヒラタジュンコ より:

    はじめまして、ヒラタです。
    マクロ初心者でこちらのサイトを参考書にして四苦八苦しています。
    よろしくお願いします。
    行の非表示の範囲指定を教えていただきたくコメントさせていただきました。

    請求データのところですでにひな形を使用しいるため、行の非表示ができません。
    A1~A15までは住所やその他のデータで使用し
    A16~A106まで明細行
    A107~A116は小計や工事費、諸経費などで使用しています。

    rowsData = wsData.Cells(Rows.Count, 1).End(xlUp).Row

    で非表示をさせると、A107~A116が非表示になってしまうのです。
    たぶん、請求データは明細だけなので非表示がきちんとされるのだろうと思いました。

    A16~A106で非表示をさせるにはどうしたらいいのでしょうか?

タイトルとURLをコピーしました