ExcelVBAでよく使うテーブル操作をまとめてみました。
テーブルのフィルタをリセット・解除する
|
1 2 3 4 |
Sub フィルタリセット() Range("テーブル名").AutoFilter Range("テーブル名").AutoFilter End Sub |
2回繰り返すだけでテーブルのフィルタをリセット（項目ごとに条件を絞る前の状態）できます。
1度だけだとフィルタが解除されるだけで、再度フィルタをセットする必要があります。
テーブルを追加する
|
1 2 3 4 5 6 7 8 9 10 11 12 |
Sub テーブル追加() Dim i As Long Dim Col As Long With Range("テーブル名").ListObject.ListRows.add.Range For i = 1 to Col .Cells(i)=Data(i) Next End With End Sub |
テーブルの最終行の下に、データを追加する方法です。
別のプロシージャなどで配列Data(i)に保存したデータを、左側の列から順に出力します。
Colは列数ですね。
・・・他にいい方法があるかも
テーブルのフィルタを操作して期間や日付で絞り込む
テーブルの日付を特定の期間や日付で絞り込む
|
1 2 3 |
Sub フィルタ日付今月() Range("テーブル名").AutoFilter Field:=列番号, Criteria1:=xlFilterThisMonth, Operator:=xlFilterDynamic End Sub |
Fieldで指定した列番号が日付の場合、期間を指定できます。Criteria1は、以下に変更できます。
| 期間 | 定数 | 値 |
|---|---|---|
| 今日 | xlFilterToday | 1 |
| 昨日 | xlFilterYesterday | 2 |
| 明日 | xlFilterTomorrow | 3 |
| 今週 | xlFilterThisWeek | 4 |
| 先週 | xlFilterLastWeek | 5 |
| 来週 | xlFilterNextWeek | 6 |
| 今月 | xlFilterThisMonth | 7 |
| 先月 | xlFilterLastMonth | 8 |
| 来月 | xlFilterNextMonth | 9 |
| 今年 | xlFilterThisYear | 13 |
| 昨年 | xlFilterLastYear | 14 |
| 来年 | xlFilterNextYear | 15 |
| 今年の初めから今日まで | xlFilterYearToDate | 16 |
※使いそうなものだけピックアップ
テーブルの日付を指定した期間で絞りこむ
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub 指定した2つの日付の期間() Dim StartDate As Date Dim EndDate As Date StartDate="日付を指定" EndDate="日付を指定" Range("テーブル名").AutoFilter 'フィルタリセット Range("テーブル名").AutoFilter 列番号, ">=" & StartDate, xlAnd, "<=" & EndDate End Sub |
StartDateとEndDateに日付を指定すれば、その期間でテーブルを操作することができます。
テーブルのフィルタ操作後にデータを取り出す
フィルタ操作後のデータの取り扱いについて、ネットで色々調べたのですが、「これだっ！」といったものが見つかりませんでした。使えそうな方法を試して、もデータがない場合エラーになったりしたので、色々試した結果、以下の形に落ち着きました。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Dim i As Long Dim Data As Variant 'テーブルから日付でソート Range("テーブル名").AutoFilter 'フィルタリセット Range("テーブル名").AutoFilter Field:=列名, Criteria1:=条件 'データの有無をチェック i=0 For Each Data In Range("テーブル名").CurrentRegion.SpecialCells(xlCellTypeVisible).Rows i=i+1 If i>=2 Then Exit For Next 'データがある場合 If i>=2 then 'データがある場合の処理 For Each Data In Range("テーブル名").SpecialCells(xlCellTypeVisible).Rows 出力先1=Data.Cells(1) 出力先2=Data.Cells(2) Next 'データがない場合 Else 'データがない場合の処理 End If 'データがある場合、ない場合共通の処理 'テーブルのフィルタをリセット Range("テーブル名").AutoFilter |
本当は、19行目の処理「For Each Data In Range(“テーブル名”).SpecialCells(xlCellTypeVisible).Rows」だけでテーブルフィルタ操作後のデータを出力したいのですが、データがない場合エラーになります。
そのため、10行目の処理で列タイトルを含む、表示されたテーブルの行数をカウントし、データがある場合（2以上）とない場合で処理を分けて行います。
ここで注意したいのが、CurrentRegionを使っており、テーブルに接するセルが使用されている場合は、うまく動作しない場合があります。
20,21行目で、フィルタ操作後のCells(列番号)でデータを取り出せます。
テーブルの列から重複しないリストを作る
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
Sub テーブルから重複しないリスト作成 'コレクションを宣言 Dim C As New Collection, i As Long On Error Resume Next 'エラー発生時スキップ 'テーブルの行数繰り返す For Each Data In Range("テーブル名[列名]") C.Add Data, Data Next On Error GoTo 0 'エラー処理無効 'コレクションの中身を出力 For i = 1 To C.Count Cells(i,1)=C(i) Next End Sub |
For Each～で、リストを作成したいテーブルの列名を指定することで、リストを簡単に作成できます。
参考にさせて頂いたサイト：Office TANAKA
テーブル操作後に表示されているテーブルから重複しないリストを作る
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Sub テーブル操作後に重複しないリストを作成() 'テーブル操作 Range("テーブル名").AutoFilter Range("テーブル名").AutoFilter 列番号, 条件 Dim C As New Collection On Error Resume Next For Each Data In Range("テーブル名[列名]").SpecialCells(xlCellTypeVisible) C.Add Data, Data Next On Error GoTo 0 End Sub |
『.SpecialCells(xlCellTypeVisible)』で表示されている範囲のみ繰り返します。
日付や性別などの条件で絞った状態で重複しないリストを作るときに便利です。
