大体でIT

-ちょっと使えるネタを紹介-

大体でIT

Excel VBAを使ってテーブルを操作(昇順・降順の並べ替え、フィルタ)する方法をまとめました。『見出し』を使って操作すると簡単なのでおすすめです。

はじめに

“見出し”を使ってテーブルを操作するメリット

メリットは、Excel VBAコードを変更する手間が省けることです。

見出しを使わないでExcel VBAのコードを書いた場合、テーブルの列の順番を変えたりテーブルに追加で列を挿入するとVBAコードを変更する必要がでてきます。

見出しを使ってテーブルを操作するVBAコードを最初から書いておけば、“テーブルに列を追加したいな”と思った場合でもVBAコードを変更する手間が省けます。

ご紹介するテーブルの操作(昇順・降順の並べ替え、フィルタ、フィルタの解除)をまとめたものがこちらになります。

VBAコードまとめ

  With ActiveSheet
      '“見出し”を使ってテーブルを昇順にします
      .Range("テーブル1").Sort Key1:=.Range("テーブル1[身長]"), Order1:=xlAscending, Header:=xlYes
      
      '“見出し”を使ってテーブルを降順にします
      .Range("テーブル1").Sort Key1:=.Range("テーブル1[身長]"), Order1:=xlDescending, Header:=xlYes  
      
      '“見出し”を使ってテーブルをフィルタします
      Fi = .ListObjects("テーブル1").ListColumns("名前").Index 'フィルタする列の順番
      .Range("テーブル1").AutoFilter Field:=Fi, Criteria1:="鈴木", Operator:=xlFilterValues
      
      'テーブルのフィルタを解除します
      .ListObjects("テーブル1").ShowAutoFilter = True
  End With

では、それぞれのVBAコードについてご説明します。

目次から使いそうな説明のところへ移動すると便利ですよ。

目次

“見出し”を使ってテーブルを昇順にする

テーブルをソートする場合は、“Sort”を使います。

昇順の場合は、“xlAscending”を指定します。

VBAコード:“見出し”を使ってテーブルを昇順にする

  With ActiveSheet
      .Range("テーブル1").Sort Key1:=.Range("テーブル1[身長]"), Order1:=xlAscending, Header:=xlYes  
  End With

ソートする列を指定する“Key1”にはソートする列の範囲(赤枠の範囲)を指定します。

このコードでは“身長”の列を昇順にしています。

ソートの場合はテーブルの“構造化参照”で.Range("テーブル[身長]")を使うとコードがシンプルになるのでおすすめです。

“見出し”を使ってテーブルを昇順にする

“見出し”を使ってテーブルを降順にする

昇順と同じように“Sort”を使います。

昇順の場合は、“xlDescending”を指定します。

VBAコード:“見出し”を使ってテーブルを降順にする

  With ActiveSheet
      .Range("テーブル1").Sort Key1:=.Range("テーブル1[身長]"), Order1:=xlDescending, Header:=xlYes  
  End With

“Key1”は同様にソートする列の範囲(赤枠の範囲)を指定します。

降順の場合も同様に“構造化参照”で.Range("テーブル[身長]")を使うとコードがシンプルになるのでおすすめです。

“見出し”を使ってテーブルを降順にする

“見出し”を使ってテーブルをフィルタする

テーブルのフィルタは、“AutoFilterを使います。

VBAコード:“見出し”を使ってテーブルをフィルタする

  Dim Fi
  With ActiveSheet
      Fi = .ListObjects("テーブル1").ListColumns("名前").Index 'フィルタする列の順番
      .Range("テーブル1").AutoFilter Field:=Fi, Criteria1:="鈴木", Operator:=xlFilterValues  
  End With

“Field”には、“名前”の列の順番(1)を指定しています。

列の順番は、“ListObjects("テーブル1").ListObjects("名前").Index”で指定することができます。

下の赤枠の“名前”の順番を指定しています。

“見出し”を使ってテーブルをフィルタする

残念ながら少しコードが長くなってしまいますが、“見出し”を使ってVBAコードを作成すると列を移動させたり列を追加してもVBAコードの変更は必要ありませんので使うメリットは大きいと思います。

テーブルのフィルタを解除する

テーブルのフィルタについて触れましたのでテーブルのフィルタを解除するVBAコードもご紹介します。

VBAコード:テーブルをフィルタを解除する

  With ActiveSheet
      .ListObjects("テーブル1").ShowAutoFilter = False  
  End With

テーブルを使うとIfなどでエラー回避する必要もなくて楽でいいですね。

おわりに

私の場合思考錯誤しながら表を作ったりするので、“やっぱりこの列はここに移動したい”、“やっぱりこの列を追加したい”というのが結構ありました。

しかもVBAコードを作りこんだ後に思ったりするんですよね。。。

そんなことがよくあったので、テーブル操作のExcel VBA作成の際には必ず“見出し”を使ってコード書いています。

ご参考になればと思います。最後までご覧くださいましてありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す