VBA

テーブルでデータ（レコード）の数をカウントする方法。即使える関数もあります。

  /
記事内に商品プロモーションを含む場合があります

このページでわかること

テーブルのデータ数をカウントする方法が分かります。
これを利用して、テーブルの複数の列範囲をいっきに取得する方法をご紹介します。

覚えること
  1. データ（レコード）数を数える
    テーブルオブジェクト.ListRows.Count

  2. 抽出後のデータ（レコード）数を数える
    テーブルオブジェクト.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

  3. 複数の列の範囲を一気に取得する
    Range(テーブルオブジェクト.ListColumns(最初の列).Range(1), テーブルオブジェクト.ListColumns(最後の列).DataBodyRange(テーブルオブジェクト.ListRows.Count))

  4. 関数を作りました。フィルター後、前対応。該当なしなら0を返す

こんにちは、hokkyokunです。
テーブルのデータ（専門用語でレコードといいます）の数を数える方法をご紹介します。

データの数を数えること自体も重要ですが、
データ（レコード）の一番下の位置を取得することにも使えます。
それが何の役に立つか？活用方法も解説します。

Contents
  1. テーブルの部位の名称について
  2. データ（レコード）の数を数えるにはListRows.Count
  3. データの一番下のセル位置を取得する方法
  4. 【追記】関数を作りました。

テーブルの部位の名称について

基本的な事項ですが、
テーブルは

  • 見出し：テーブルオブジェクト.HeaderRowRange
  • データ（レコード）：テーブルオブジェクト.DataBodyRange
  • 集計行：テーブルオブジェクト.TotalsRowRange

の三つの部位から出来ています。

これを図にするとこんな感じです。
それぞれの位置を取得するには

例えばデータ（レコード）の一行目の左端であれば
テーブルオブジェクト.DatabodyRange(1)とインデックス番号で指定します。
一行目の二番目は テーブルオブジェクト.DatabodyRange(2)です。

データ（レコード）全体を指すには
テーブルオブジェクト.DatabodyRangeと指定します。

データ（レコード）の数を数えるにはListRows.Count

基本的な操作

構文：テーブルオブジェクト.ListRows.Count

使ってみましょう。
例えば、こんな感じで表を作ったとし、
データ（レコード）の数を数えてみます。

Sub データ数カウント()

Dim table As ListObject
Set table = ActiveSheet.ListObjects(1)

MsgBox ("データ（レコード）の数は：" & table.ListRows.Count)

End Sub

データの数を数えることが出来ました。

抽出後のデータの数

次は抽出後のデータの数を数えて見ましょう。

上記のプログラムをそのまま動かしてみると・・・

変わらず、7です。
このプログラムはテーブルのデータ数のカウントなので、
抽出後（見えている行）のカウントはしてくれません。

SpecialCellsプロパティを使います。

構文：テーブルオブジェクト.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)

SpecialCellsは文字通り特殊なセルのみを取得するプロパティで
引数を xlCellTypeVisible にすることで
抽出後（＝見えている行）のセルのみを取得することが出来ます。

注意するべきはListColumnsを使っていることです。

考え方としては

一つの列（＝listColumns（1）とするのが無難)のデータ（レコード）の中の特殊なセル（=Specialcells(xlCellTypeVisible))の数を数える

ということです。

プログラムはこうなります。

Sub 抽出後データ数カウント()

Dim table As ListObject
Set table = ActiveSheet.ListObjects(1)

MsgBox ("データ（レコード）の数は：" & table.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Count)

End Sub

データの一番下のセル位置を取得する方法

さて、上記の方法を使えばデータ（レコード）の最下段の位置を取得することが出来ます。
先ほどから使用している表で最下段（スイカ）を取得してみようと思います。

Sub データの最下段の取得()

Dim table As ListObject
Set table = ActiveSheet.ListObjects(1)
dim maxRow as long

MsgBox ("「果物」列のデータ（レコード）の最下段の位置は：" & table.ListColumns("果物").DataBodyRange(table.ListRows.Count).Address & vbCrLf & _
        "「果物」列データ（レコード）の最下段の値は：" & table.ListColumns("果物").DataBodyRange(table.ListRows.Count).Value)

End Sub

範囲取得が出来る

どうしてデータの最下段を取得する必要があるかというと
テーブルの範囲を取得するときに使えるからです。

テーブルの範囲取得って基本的には

  • 見出し、データ（レコード）、集計行を全体がばっととるか
  • 列の中の 見出し、データ（レコード）、集計行 をとるかしか想定されていません。

なので、下記でいうところの「日付」列から「スーパー」列の範囲を取得するってなると
意外にハードルが高いです。

基本的に複数の列の範囲を一発でとることは想定されていません

ベタ打ちでよければいくらでも作るんですが、
列が増えたり減ったり、データが増えたり減ったりしても大丈夫な表を作るには
選択肢は限られてきます。

これが唯一の方法ではないですが、
私は割りと好きな方法なので、ご紹介しておきます。

Sub 特定の複数列範囲の取得()

Dim table As ListObject
Dim Rng As Range
Set table = ActiveSheet.ListObjects(1)

Set Rng = Range(table.ListColumns("日付").Range(1), table.ListColumns("スーパー").DataBodyRange(table.ListRows.Count))

MsgBox ("「日付」列から「スーパー」列までの範囲は：" & Rng.Address)
End Sub

Unionでつなげることでももちろん作れます。
たくさんの列をつなげるとなると、
大変だし、For文でつなげることを考えると案外コード少なく処理できるかなと思います。

テーブルは自由に処理できる部分とそうでもない部分があり、ちょっと面倒ですが、
使うメリットは大きいと思いますので、うまく処理してければと思います。

ではでは。

【追記】関数を作りました。

久々にマクロ触ってテーブルのカウントをする必要があったので
関数作りましたので共有させていただきます。

Function count_Table_rows(ByVal table As ListObject, ByVal visibleRow As Boolean)
    If Not visibleRow Then
        count_Table_rows = table.ListRows.Count
        
    Else
        On Error GoTo Err
        
        count_Table_rows = table.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible).Count
    End If
    
Err:
    If Err.Number = 1004 Then
        count_Table_rows = 0
    End If
    
End Function

簡単に使い方をご紹介します。

引数table対象となるテーブル
データ型はlistobject
visibleRowフィルター後の行数を調べたいならTrue
フィルターをかけないテーブル全行数はFalse
戻り値行数
フィルターをかけて該当がなければ０を返す

例えばざっくりですが、以下のような表があったとします。

ここからリンゴだけ抽出します。

行全体のカウントと抽出後の行数をカウントしてみます。

Sub test()

Dim table As ListObject

Set table = ThisWorkbook.Worksheets(1).ListObjects(1)

'フィルター後の「リンゴ」行の数
Dim count_appleRows As Integer
count_appleRows = count_Table_rows(table, True)
Debug.Print ("リンゴ行:" & count_appleRows)

'テーブル全体の数
Dim count_allRows As Integer
count_allRows = count_Table_rows(table, False)
Debug.Print ("テーブルの全行:" & count_allRows)


End Sub

次にVBAでフィルターをかけて該当なしになった場合を試してみます。

Sub test2()

Dim table As ListObject

Set table = ThisWorkbook.Worksheets(1).ListObjects(1)

table.Range.AutoFilter field:=table.ListColumns("果物").Index, Criteria1:="リン"

'フィルター後の行数
Dim count_nothingRows As Integer
count_nothingRows = count_Table_rows(table, True)
Debug.Print ("フィルターの行:" & count_nothingRows)


End Sub
RELATED POST