セル範囲をテーブル化する方法が分かります。
テーブルの部位の操作が分かります。
-
テーブルの作成(セル範囲のテーブル化)
シートオブジェクト.ListObjects.Add(, レンジオブジェクト(テーブル化する範囲), , xlYes)
-
テーブルの作成(セル範囲のテーブル化)名前を付けるVer
シートオブジェクト.ListObjects.Add(, テーブル化する範囲 , , xlYes).name= テーブルの名前
-
テーブルのセットの仕方
シートオブジェクト.ListObjects(1)
レンジオブジェクト.ListObject
シートオブジェクト.ListObjects(テーブル名)
-
テーブルの解除
テーブルオブジェクト.TableStyle = “”
テーブルオブジェクト.Unlist
-
テーブル部位の取り方
ごめんなさい。長いので本文を見てください…
こんにちはhokkyokunです。
テーブルの基本的な使い方をご紹介します。
テーブルの作成(セル範囲のテーブル化)
構文:シートオブジェクト.ListObjects.Add(SourceType,Source,LinkSource,XlListObjectHeaders,Destination,tablestylename ] )
SourceType:元データの種類→外部データ等を使わない限り、省略して大丈夫です。
Source:元データのセル範囲→テーブル化したい範囲を指定しましょう。
LinkSource:外部データとリンクするかどうか→外部データを使わない限り、省略して大丈夫です。
XlListObjectHeaders:一番上の行を見出しとみなすかどうか→エクセルが判断してくれるモードもありますが、おすすめはxlYes(見出し行とする)
Destination:外部データ紐付けの場合、作成する場所を指定します。
tableStyleName:テーブルの色身を指定できます。”TableStyleLight2”のように指定→お好みに合わせえて
お勧めの構文
引数多く複雑ですが、使うものはそんなに多くありません。
以下の形をお勧めします。
シートオブジェクト.ListObjects.Add , テーブル化する範囲 , , xlYes
この形をコピペで大丈夫です。
他の機能はこれで対応できないときに使えばいいです。
テーブルの名前を指定したいときは
シートオブジェクト.ListObjects.Add(, テーブル化する範囲 , , xlYes).name= テーブルの名前
どちらかを使えば大丈夫です。
こんな感じで表を作ったとします。
これをテーブル化してみましょう。
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/image-68.png)
Sub テーブル化()
ActiveSheet.ListObjects.Add(, ActiveSheet.Range("A1").CurrentRegion, , xlYes).Name = "果物お買い物リスト"
End Sub
こんな感じでテーブル化できました。
テーブル名もつけることができました。
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/image-70.png)
テーブルのオブジェクト変数のセットの仕方
テーブルのオブジェクト変数への格納の仕方ですが、
よく使うのは3種類です。
それぞれ都合のよいものを使うようにしましょう。
シートオブジェクト.ListObjects(1) レンジオブジェクト.ListObject シートオブジェクト.ListObjects(テーブル名)
Listobjects(1)というようにインデックス番号でもセットすることができるのですが、
この場合、同じシート内にすでにテーブルがある場合は注意が必要です。
基本的に同じシートでテーブルが作られた順に番号が振られるので、
複数テーブルが有る場合は、レンジかテーブル名で指定をしましょう。
上記のテーブルを変数にセットしてみましょう。
Sub テーブル変数セット()
Dim table1 As ListObject
Dim table2 As ListObject
Dim table3 As ListObject
'下記のいずれかを使用してください
Set table1 = ActiveSheet.ListObjects(1)
Set table2 = ActiveSheet.Range("A1").ListObject
Set table3 = ActiveSheet.ListObjects("果物お買い物リスト")
Debug.Print "table1のテーブル名は:" & table1.Name
Debug.Print "table2のテーブル名は:" & table1.Name
Debug.Print "table3のテーブル名は:" & table1.Name
End Sub
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/image-72.png)
テーブルの解除
テーブルを解除したい場合もあると思います。
構文:テーブルオブジェクト.Unlist
ただし、注意が必要です。
テーブルにすると色が縞々になると思いますが、
解除するとこの縞々は残ることになります。
このテーブルを解除してみます。
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/image-73.png)
Sub テーブル解除()
Dim table As ListObject
Set table = ActiveSheet.ListObjects(1)
table.Unlist
End Sub
テーブルは解除されましたが、
しましまは残っています。
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/image-74.png)
しましまも解除するためには
テーブルオブジェクト.TableStyle = “”
を入れると、しましまは解除されます。
TableStyleはしましまの様式を編集できるプロパティなので、
こだわりがあるときや、縞々を入れたくないときには使ってみてください。
よって、解除するときは以下のプログラムが良いと思います。
Sub テーブル解除()
Dim table As ListObject
Set table = ActiveSheet.ListObjects(1)
table.TableStyle = ""
table.Unlist
End Sub
テーブルの部位
テーブルの範囲の取り方は独特です。
普通のRangeの感覚とは全然違いますので、最初は戸惑うかもしれませんが、
なれたらすごく便利です。
テーブルは基本的に、見出し、データ、集計行からなっています。
見出し:テーブルオブジェクト.HeaderRowRange
データ(レコード):テーブルオブジェクト.DataBodyRange
集計行:テーブルオブジェクト.TotalsRowRange
範囲の取得の仕方も独特
範囲の取り方も独特です。
上から
全体(Range)> 見出し、データ(レコード)、集計行の範囲(HeaderRowRangeなど) > 見出し、データ(レコード)、集計行の範囲一つ(Range(1)やHeaderRowRange(1)など)
単一のセルを指す場合は後ろに(インデックス番号)を付けます。
例えば テーブルオブジェクト.HeaderRowRange (1)みたいな感じです。
![](https://www.hokkyokun.com/wp-content/uploads/2021/08/image-53.png)
例えば、
下記表で色々範囲を取ってみようと思います。
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/image-76.png)
Sub テーブル範囲取得()
Dim table As ListObject
Dim str As String
Set table = ActiveSheet.ListObjects(1)
str = "Range:" & table.Range.Address & vbCrLf & _
"HeaderRowRange:" & table.HeaderRowRange.Address & vbCrLf & _
"DataBodyRange:" & table.DataBodyRange.Address & vbCrLf & _
"TotalsRowRange:" & table.TotalsRowRange.Address & vbCrLf & _
"見出しの一番目のセル位置:" & table.HeaderRowRange(1).Address & vbCrLf & _
"データ(レコード)の一番目のセル位置:" & table.DataBodyRange(1).Address & vbCrLf & _
"データ(レコード)の最後のセル位置" & table.DataBodyRange(table.DataBodyRange.Count).Address & vbCrLf & _
"集計行の一番目のセル位置:" & table.TotalsRowRange(1).Address
MsgBox str
End Sub
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/image-77.png)
列の取得も可能
テーブルは列の取得も可能です。
こちらも少しクセがありますので、具体例で見ていきたいと思います。
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/image-76.png)
Sub テーブル列範囲取得()
Dim table As ListObject
Dim str As String
Set table = ActiveSheet.ListObjects(1)
str = "一番目の列の範囲: " & table.ListColumns(1).Range.Address & vbCrLf & _
"「年月」列の範囲: " & table.ListColumns("年月").Range.Address & vbCrLf & _
"「年月」列の見出し範囲: " & table.ListColumns("年月").Range(1).Address & vbCrLf & _
"「年月」列のデータ(レコード)の範囲: " & table.ListColumns("年月").DataBodyRange.Address & vbCrLf & _
"「年月」列の集計行の範囲: " & table.ListColumns("年月").Range(table.ListColumns("年月").Range.Count).Address
MsgBox str
End Sub
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/image-78.png)
ListColumnsにはRangeとDataBodyRangeしか用意されていないので、注意が必要です。
見出しはRange(1)
集計行はRange(Range.Count)で
取得できます。
複数の列を取得するプロパティはない
残念ながら複数の列を簡単に取得することはできません。
Unionでひたすらつなげるのが一般的かと思います。
やってみましょう。
Sub テーブル列複数取得Unionバージョン()
Dim table As ListObject
Dim str As String
Dim Rng As Range
Set table = ActiveSheet.ListObjects(1)
Set Rng = Union(table.ListColumns("果物").Range, table.ListColumns("個数").Range, table.ListColumns("単価").Range)
MsgBox ("列「果物」、「個数」、「単価」の範囲: " & Rng.Address)
End Sub
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/image-79.png)
これくらいならUnionだけでも充分ですが、
たくさんあると厳しいですよね…
そんなときはFor文を使いましょう。
Sub テーブル列複数取得Forバージョン()
Dim table As ListObject
Dim Rng As Range
Dim i As Long
Set table = ActiveSheet.ListObjects(1)
Set Rng = table.ListColumns(2).Range
For i = 3 To 4
Set Rng = Union(Rng, table.ListColumns(i).Range)
Next i
MsgBox ("列「果物」、「個数」、「単価」の範囲: " & Rng.Address)
End Sub
![](https://www.hokkyokun.com/wp-content/uploads/2021/09/image-80.png)
とびとびの場合はやっかい
For文はとびとびの列を結合するのは不得意です。
例えば1列目と3列目と7列目を取得したいという場合は
For文では難しいです。
一方、Unionだけでつなげる場合は
コードが冗長になり、面倒なプログラムになっちゃいます。
なので、配列を使ってうまく、コード量を減らす工夫が必要かと思います。
いずれにしても複数の列を取得することを想定して作られていないので、
クラスで定義するか、
関数を作るかしないと
ちょっと面倒なコードになっちゃいますね
個人的には
SQLを使って表を加工して取得が一番手っ取り早い気がします。
エクセルだけで処理をするというのは
ものによっちゃ、限界がある
というのが現実なのかも知れないです。
ではでは