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

★気に入ったらシェアをお願いします!


Rows

siskaV / Pixabay

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

請求データ一覧から請求書を自動で作成するシリーズのVBA講座、第3回です。

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

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

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

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

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

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

スポンサーリンク

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

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

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

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

片手落ちの状態でしたね…てへ。

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

Rows.Countで行数がいくつなのか調べる

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

そのような場合は、

Worksheetオブジェクト.Cells(Rows.Count, 列数).End(xlUp).Row

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

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

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

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

従いまして実際には

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

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

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

For~Nextの最終値は…?

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

例えばデータ一覧が

データの品目数は6

ですと、For~Next文は2行目から7行目までを繰り返してほしい、ということになりますね。

Rows.Countで取得したrowsDataには1列目の最終データがある行、つまり”7″が入ります。

1行目は見出し行なので、品目数としてはカウントしてはいけませんね。従いまして-1をします。

これで良さそうです。

請求書ひな形も可変にしないといけない

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

実行結果

…ってあれ?

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

これも面倒なので解消しておきましょう。

請求書ひな形をバカ長くする

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

請求書ひな形を長く

はい、長いですね。

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

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

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

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

Rows~.Hiddenで不要な行を隠す

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

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

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

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

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

従いまして今回の例では

と書きます。

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

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

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

行を隠した実行結果

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

気持ちいいですね~。

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

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

と書けばOKです。

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

まとめ

  • Rows.Countを使ってデータの最終行を求める
  • Rows~.Hiddenで不要な行を隠すまたは再表示する

などのテクニックを駆使して、請求データのデータ数が可変だった場合でも請求書を作れるようになりました。

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

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

次回はこちらの記事です。

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

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

合わせてご覧下さいね!

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

  1. 【初心者向けエクセルVBA】データ一覧から請求書を自動で作る
  2. 【初心者向けエクセルVBA】For~Next文で簡潔にプログラムを書く
  3. 【初心者向けエクセルVBA】行の数をカウントする&不要な行を隠す
  4. 【初心者向けエクセルVBA】文字列の連結&Format関数での書式変更
  5. 【初心者向けエクセルVBA】If~Thenを使った条件分岐の超入門
  6. 【初心者向けエクセルVBA】Public変数の宣言とSubプロシージャの呼び出し
  7. 【初心者向けエクセルVBA】InputBoxでの日付入力と月末日の自動算出
  8. 【初心者向けエクセルVBA】Worksheetのコピーを活用して複数の請求書を作る
  9. 【初心者向けエクセルVBA】ファイルのコピーを使って取引先別の請求書を作る


コメント

  1. ハム より:

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

    2つ、質問があります。

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

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

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

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

    • ハムさん

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

      まず

      Dim strtotal As String

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

      また

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

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

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

      • ハム より:

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