VBAで行数をカウントする方法【エクセル業務の効率をアップさせるCount関数の使い方】

Left Caption

疑問がある人

一覧表の行数が変わったときに自動的に行数を調べる方法ってないの?

こんな疑問にお答えします。

結論からいうと、VBAでカウント関数を設定すれば、一覧表の行数が変わっても自動的に対処することが可能です。

 

VBAでカウント関数を上手に扱うことができれば、エクセル業務の効率を格段にアップさせることができます。

カウント関数でどんなことができるのか、実際の仕事にどのようにカウント関数を導入することができるのか、をサンプル事例を使って詳しくご紹介します。

 

VBAを使って行数をカウントすると何ができるのか?

 

 

VBAを使って行数をカウントすると、一覧表で値が入力されたセルの行数を取得することができる!ということです。これはとても便利な機能でいろんなことにおうようすることができます。

行数をカウントすることで、以下のような処理を完全自動化することができます。

 

  • 一覧表の罫線を引く作業
  • 集計表の合計計算
  • 大量のデータの統計分析

エクセルを使って大量のデータ分析を手作業で行っている方はカウント関数を使って自動化することがおすすめです。

私の職場では、みんなでデータを編集するエクセルファイルがほとんどで、「知らないうちに一覧表の行数が変更されるた」「罫線がぐちゃのぐちゃの保存された」ということが頻繁に発生しています。

行数が変更されていたことに気づかず資料を報告してしまい問題になった!という事象が発生したこともあります。

行数をカウントするVBAを設定することで、このような問題の発生を防ぐことができます。

 

行数をカウントするカウント関数とは?

 

カウント関数とは、文字通り「指示した内容の数量を数えてくれる関数」です。

以下のようなときにカウント関数はよく利用されます。

 

  • 一覧表の一番下は何行目?
  • 一覧表に入力されたデータ数は何個?
  • 「鈴木」というテキストは何個記載されている?

 

Right Caption

悩んでる人

画面を見て行数を数えたり、フィルターをかけてから「鈴木」を数えている方を見かけますが、効率の悪い作業を行っていると思います。

カウント関数を使えば、VBAを実行するたび数量を数えてくれるので、目で見て数量を数える行為をしなくて済むようになります。

簡単なサンプル事例を使ってカウント関数の基本的な設定方法について説明していきます。

基本的な設定はもう知っている!という方は、記事の後半で「実務で使えるカウント関数の設定方法」が詳しく紹介されているので参考にしてみてください。

 

サンプル事例 行数を取得するカウント関数の使い方

下図のような事例を使ってVBAを作成していきます。

一覧表の一番下の人は何番目で名前はなんのかを導き出すVBAを作成していきます。

 

1.VBAを起動

 

2.標準モジュールを作成

 

3.ModuleにサンプルVBAを記載

 

 

4.サンプルVBA

1 Sub テスト()
2 Cells( 2 , 3 ) = Cells( Rows.Count,1 ).End(xlUp).Row
3 Cells( 5 , 3 ) = Cells(Cells(Rows.Count,1).End(xlUp).Row,1)
4 End Sub

 

5.ボタンを作成

 

6.作成したボタンにマクロを登録

マクロ名のリストにはModuleに作成したサンプルVBAが自動的に表示されます。「テスト」を選択して「OK」を押すと作成したボタンにサンプルVBAが登録されます。

 

7.サンプルVBAの実行

一覧表の内容を変更したり、行数を増やしても正しい数字と名前が記載されます。

 

Left Caption

上を目指す人

数字やテキストを自動的に表示させることができるなんてとても便利!

 

サンプル事例で使われているVBAは、

変数 = Cells(Rows.Count , 1).end(xlUp).Row

です。

 

一列目の一番下に入力されているデータの行が何番目なのかカウントし、変数に数字を入れる!という意味です。変数をプログラム内で記載すれば、いつでも「1列目の最後の行数(数字)」を使うことができます。

サンプルVBAでは、取得した最後の行数を変数ではなくセルに直接反映させているため、「7」という数字がC2セルに表示されています。

 

Left Caption

ひらめく人

このVBAはかなり使用頻度が高いので、1文全て暗記してしまいましょう。

 

カウント関数を上手に使えば、最後の行に入力されている値(テキスト)を取得することもできます。大量のデータ整理を行うようなエクセルファイルにこのVBAを導入すると作業効率を格段にアップさせることができるので、自由に扱えるようにしましょう。

 

実務で使える行数をカウントするVBA

 

下記で紹介している3つのサンプル事例は実際の仕事で使えるVBAが使われています。エクセル作業の効率をアップさせたい方は是非参考にしてみてください。

 

サンプル事例① 一覧表の行数をカウントして罫線を引くVBA

罫線を自動で調整するVBAを使えば、「手動で罫線を引き直す」という手間のかかる一切なくなります。

 

Right Caption

拒否する人

罫線がグチャグチャのまま資料を提出するなんてありえません

罫線を自動で調整するVBAの導入方法とサンプルVBAをご紹介します。

上記のような一覧表の罫線を自動的に引いてくれるVBAを設定していきます。

 

1.VBAを起動

 

2.標準モジュールを作成

 

3.ModuleにサンプルVBAを記載

 

4.サンプルVBA

1 Sub テスト()
2 g = Cells(Rows.Count, 1).End(xlUp).Row
3 r = Cells(1, Columns.Count).End(xlToLeft).Column
4 Range(Cells(1, 1), Cells(g, r)).Borders.LineStyle = xlContinuous
5 Range(Cells(1, 1), Cells(g, r)).Borders.Weight = xlThick
6 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideVertical).LineStyle = xlContinuous
7 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideVertical).Weight = xlThin
8 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
9 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideHorizontal).Weight = xlThin
10 End Sub

上記サンプルVBAをコピーしてModuleにペーストしてください。

 

5.ボタンを作成

 

6.作成したボタンにマクロを登録

マクロ名のリストにはModuleに作成したサンプルVBAが自動的に表示されます。「テスト」を選択して「OK」を押すと作成したボタンにサンプルVBAが登録されます。

 

7.サンプルVBAの実行

 

設定したVBAを実行すると、自動的に一覧表の罫線を引くことができました。

 

一覧表の項目が変更されたら自動的に罫線を引き直してくれるかどうか試してみます。

 

上図のように適当に項目を追加してVBAを実行してみます。

このVBAを使えば、一覧表の編集によって表の罫線を引き直す!という処理を防止することができます。上記で掲載しているサンプルVBAはA1セルから始まる一覧表の罫線を自動で調整するものです。

 

Left Caption

ひらめく人

サンプルVBAをコピペして使ってみてください。

 

 

下記の記事では、罫線を操作するVBAについて詳しく紹介しています。

 

罫線の範囲を自動調整するVBAはかなり使用頻度が高いVBAなので、自由に扱えるようにしておきましょう。

 

 

サンプル事例② 一覧表の外側にコメントを入力する

一覧表の行数をカウントすることで、一覧表の枠外にコメントを記入することができます。一覧表が増えても一番下の行までデータを分析することができるので、コメント記入漏れの心配がありません。

一覧表の枠外にコメントを記入するVBAの導入方法とサンプルVBAをご紹介します。

上記のような処理を行うVBAを設定していきます。

 

1.VBAを起動

 

2.標準モジュールを作成

 

3.ModuleにサンプルVBAを記載

 

4.サンプルVBA

1 Sub テスト2()
2 g = Cells(Rows.Count, 1).End(xlUp).Row
3 r = Cells(1, Columns.Count).End(xlToLeft).Column
4 Range(Cells(1, 1), Cells(g, r)).Borders.LineStyle = xlContinuous
5 Range(Cells(1, 1), Cells(g, r)).Borders.Weight = xlThick
6 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideVertical).LineStyle = xlContinuous
7 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideVertical).Weight = xlThin
8 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
9 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideHorizontal).Weight = xlThin
10 For i = 2 To g
11 If Cells(i, 2) > 400 Then
12 Cells(i, r + 1) = “値段が高い"
13 Else
14 End If
15 Next
16 End Sub

 

5.ボタンを作成

 

6.作成したボタンにマクロを登録

マクロ名のリストにはModuleに作成したサンプルVBAが自動的に表示されます。「テスト2」を選択して「OK」を押すと作成したボタンにサンプルVBAが登録されます。

 

7.サンプルVBAの実行

 

一覧表の項目が増減しても上の実行結果のように枠外(右側)にコメントが入力されます。一覧表の項目を増やしたり、減らしたりしても枠外にテキストが入力されます。

 

サンプル事例③ 一覧表の一番下に合計を集計するVBA

集計するVBAは、行の挿入や削除が頻繁に行われても集計した金額に影響を与えることを防ぐことができます。

VBAを実行する度に集計し直すので、「挿入した行の値が集計されていなかった!計算式が消されて合計金額がわからなくなっていた!」という心配がありません。

行数が変更される一覧表の集計を求めるときに便利なVBAです。

 

上図のような処理を行うVBAを設定していきます。

1.VBAを起動

 

2.標準モジュールを作成

 

3.ModuleにサンプルVBAを記載

 

サンプルVBA

1 Sub テスト3()
2 g = Cells(Rows.Count, 1).End(xlUp).Row
3 r = Cells(1, Columns.Count).End(xlToLeft).Column
4 Range(Cells(1, 1), Cells(g, r)).Borders.LineStyle = xlContinuous
5 Range(Cells(1, 1), Cells(g, r)).Borders.Weight = xlThick
6 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideVertical).LineStyle = xlContinuous
7 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideVertical).Weight = xlThin
8 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideHorizontal).LineStyle = xlContinuous
9 Range(Cells(1, 1), Cells(g, r)).Borders(xlInsideHorizontal).Weight = xlThin
10 Cells(g + 1, 1) = “合計"
11 Cells(g + 1, 2) = WorksheetFunction.Sum(Range(Cells(2, 2), Cells(g, 2)))
12 End Sub

 

5.ボタンを作成

 

6.作成したボタンにマクロを登録

 

マクロ名のリストにはModuleに作成したサンプルVBAが自動的に表示されます。「テスト3」を選択して「OK」を押すと作成したボタンにサンプルVBAが登録されます。

 

7.サンプルVBAの実行

 

一覧表の行数や金額を変更してVBAを実行してみると下図のような結果になりました。

 

行の挿入や削除が頻繁に行われて、計算式がぐちゃぐちゃになってしまうことはよくあることですが、計算が狂っていることに気づかず資料を作成しまうのはあってはならないことです。

合計を集計するVBAを使えば、実行毎に集計をやり直すので計算式がぐちゃぐちゃにされる心配も集計が間違う心配もありません。

 

Left Caption

ひらめく人

大量のデータ分析、集計作業はカウント関数が導入されたVBAの設定がおすすめです。

 

これからVBAを勉強をはじめるには

カウント関数のように、VBAには仕事の効率をアップさせることができる機能がいろいろあります。VBAのプログラミングスキルを身に着けることで、今まで何時間もかかかっていた作業をほんの数分で処理することができるようになります。

VBAを基本から学びたい!という方におすすめなのが、オンラインプログラミング学習サービス【1st Step】です。

【1st Step】は初心者のためのプログラミング学習サービスで、C言語・java・VBAの3つのプログラミングの基礎をオンラインで勉強することができます。

 

  • 基礎からプログラミングを勉強したい
  • まとまった勉強時間の確保が難しい
  • 途中で挫折したくない

上記のように考えている方に【1st Step】はおすすめです。

下記の記事では、オンラインプログラミング学習サービス【1st Step】の特徴や私の体験など詳しく紹介しています。

これからVBAの勉強を始めようと考えている方は是非参考にしてみてください。