今回の記事では
フィルターで条件を絞り込んだ一覧表の結果を配列に入れるためにはどうしたら良いの？
今見えている項目だけ配列に格納するにはどうしたら良いの良くわからないから教えて欲しい
フィルターで絞り込んだ内容を配列に格納することは出来たけどその結果を書き出すにはどうしたら良いの？
格納してあるデータを別のシートに転記する方法をなるべく簡単に教えて欲しい
こういった疑問に答えます
✔VBAでフィルターで絞り込んだ項目だけ転記する方法のテーマ
①フィルターで絞り込んだ条件を配列に格納するには「可視セル」を上手く扱うのが最適解です
②配列に格納されたデータを別のシートに転記する時は配列の最大数を活用するのが最適解です
この記事を書いているのはOfficeを約20年間活用しながら
ITを活用したエステの経営マネジメントしている私の経験を基に
VBAでフィルターで絞り込んだ項目だけ転記する方法に関して実体験に基づいて解説しています
今回の内容を一緒にやりたい場合は▼のファイルをダウンロードして下さい
目次を使って読みたい項目へ
フィルターで絞り込んだ条件を配列に格納するには「可視セル」を上手く扱うのが最適解です
フィルターで絞り込んだ条件を配列に格納するには可視セルを上手く扱ってデータを格納するのが最適解です
なぜなら、可視(かし)セルつまり見えているセルを上手く扱うことでフィルターで絞り込んだデータを配列に格納することが出来るからです
Excelのテーブル機能などで使うフィルターを使って絞り込んだデータをVBAの配列に格納して、その結果を転記したい時ってありますよね
例えば私の場合こんなケースがあります
【目的】
条件に合った項目の一覧表をCSVファイルとして保存したい
【手段と手順】
- テーブル機能で作った一覧表を条件に合わせてフィルターで絞り込む
- 絞り込んだ結果を配列に格納する
- 新しいシートを追加
- 格納された配列の結果を追加したシートに貼り付ける
- 貼り付けたシートをCSVファイルとして保存
- 追加したシートを削除する
実務だとこんな感じケースは結構あります
ただ、この可視セルの結果だけをコピペするのは意外と大変なので今回は、別のシートに可視セルの結果を表示(貼り付ける)方法について解説します
前半では配列に可視セルを格納するところまでを解説し、後半では転記する方法について解説します
まずは、可視セルを転記した結果を先に見ておきましょう
ベースなるのがこんな感じの表です
分かりやすいように「連番と数字」だけあります
作業としては「数字の値」の条件を変えてフィルターの設定をします
今回は「数値フィルター」から「指定の値以上」をクリック
テストとして「50」以上の値を指定
「OK」ボタンをクリック
このフィルター設定はテストなので何でもOKです
その結果、条件に合う一覧表が完成します
ココで今回用意してある「可視の転記」を選択
「実行」をクリック
その結果「元データ」というシートの結果を自動で「転記先」のシートに転記できました
ではこのコードを見てみましょう
Option Explicit
Sub 可視の転記()
Dim VsbCel As Long '可視セルの取得用
Dim GetAly() As Variant '項目の取得用配列
Dim i As Long '配列の要素設定カウンター
Dim j As Long '2次元配列の要素設定カウンター
Dim n As Long '配列の調整用カウンター
Dim m As Long '2次元配列の調整用カウンター
Dim MaxRow As Long 'テーブルの最終行数の取得用
Dim MaxCol As Long 'テーブルの最終列数の取得用
With Worksheets("元データ")
'可視セルをカウント
' VsbCel = .Cells(1, 1).CurrentRegion.Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1
VsbCel = .Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).SpecialCells(xlCellTypeVisible).Count - 1
'最終行・列数の取得
MaxRow = .Cells(Rows.Count, 1).End(xlUp).Row
MaxCol = .Cells(1, Columns.Count).End(xlToLeft).Column
'配列数の設定
ReDim GetAly(VsbCel, MaxCol - 1)
n = 0
m = 0
'配列に表示されたセルの内容を格納する
For i = 1 To MaxRow
If .Rows(i).Hidden = False Then
For j = 1 To MaxCol
GetAly(n, m) = .Cells(i, j).Value
m = m + 1
Next j
n = n + 1
m = 0
End If
Next i
End With
'配列の内容を転記する
With Worksheets("転記先")
.Cells.Clear
.Cells(1, 1).Resize(UBound(GetAly, 1) + 1, UBound(GetAly, 2) + 1) = GetAly
.Select
End With
End Sub
Step1 可視セルをカウントする方法
まずは、可視セルをカウントする方法について見ていきましょう
尚、変数の部分の解説は省略させてもらいますね
'可視セルをカウント
' VsbCel = .Cells(1, 1).CurrentRegion.Resize(, 1).SpecialCells(xlCellTypeVisible).Count - 1
VsbCel = .Range(.Cells(1, 1), .Cells(.Cells(Rows.Count, 1).End(xlUp).Row, 1)).SpecialCells(xlCellTypeVisible).Count - 1
このソースコード(以降コード)の部分は表の最大行数を可視セルに限定して数える方法です
2つのパターンを作っていますが、コメントアウトしている方がよく薦められているパターンですがある程度VBAの知識が無いと理解しづらいです
2つ目のコードはちょっと分かりづらいですが、VBAを初めた頃にでてくる方法を使っています
どちらも結果は同じなので好きな方を使って下さい
今回は1つ目のコードを参考に動きを図解にしてみましょう
「VsbCel = .Cells(1, 1).CurrentRegion.Resize(, 1).SpecialCells(xlCellTypeVisible).Count – 1」
まず、前提として「VsbCel」という変数に可視セルの最終行数(配列の数)をセットします
可視セルの範囲を指定するために「.Cells(1, 1).CurrentRegion」で対象範囲を自動で取得させます
この部分は「Cells(1, 1)」を基準に「CurrentRegion(カレント・リージョン)」で自動的に値が入力されている範囲を設定してくれます
「CurrentRegion」は直訳で「現在の地域」という意味があり自動で取得した範囲を「1つのかたまり」として扱ってくれます
これなら、手動で範囲を指定しないで良いので簡単ですね
ちなみに2つ目の方法は1列目の最大行数を取得する方法を使っています
次が「.Resize(, 1)」の部分ですね
「Resize(リサイズ)」はその名の通りサイズを再設定するという意味です
この部分も図解してみましょう
これは最初に「CurrentRegion」で全ての範囲を取得しましたよね
ただ、最終行を数えたいのは1列目だけです
じゃないと例えば1列で10行ある2列の表の場合、行数を数えると「10行×2列=20行」になっちゃいます
そこで「.Resize(, 1)」で指定している範囲の「行数は何もしない」で「列数を1列に限定」してね
と設定しています
そのため「.Resize(, 1)」は「.Resize(行数, 列数)」ですが「何もしない=空白」という指定ができるのでこういう書き方をすると1列目に限定することが出来るという訳です
次の「.SpecialCells(xlCellTypeVisible)」です
この部分が今回のポイントです
「.SpecialCells」は簡単に言うと条件に合ったセルという意味です
その条件とは「(xlCellTypeVisible)」で「見えているセル」を指定しています
「Visible(ビジブル)」とは「見える」という意味なので「セルのタイプは見えているもの」と指定しているという訳です
そして最後の「.Count – 1」で数を数えるという結果から「1」を引いています
つまり「.SpecialCells(xlCellTypeVisible).Count」で見えている数を数えます
この範囲の数を数えると答えは「7」ですねタイトルも含めるので
最後の仕上げに「-1」をする理由ですがこれは2次元配列用に数字を調整しています
2次元配列については次に触れますが配列は「0」から始まる空の箱(コンテナ)に値を格納していきます
つまり配列とは簡単に言うとPCにデータを覚えておいてもらう作業のことです
今回数えた行数はいくつ配列を用意する必要があるのか？ というのを数える作業なので見えるセルを数えた結果から「-1」をして調整することで配列という箱の数を調整したという訳です
ちょっと分かり辛いので慣れないうちは「-1」で調整すると覚えておけばOKです
Step2 2次元配列を準備する
では今回の目的の1つである配列に値を格納していきましょう
ただ、一気に話を進めると分かりづらいのでまずは2次元配列の箱の用意をします
'最終行・列数の取得
MaxRow = .Cells(Rows.Count, 1).End(xlUp).Row
MaxCol = .Cells(1, Columns.Count).End(xlToLeft).Column
'配列数の設定
ReDim GetAly(VsbCel, MaxCol - 1)
この部分が配列を準備している箇所です
「MaxRowとMaxCol」は1列目の最終行数を取得するのと1行目の列数を取得するということをしています
この2つは単純なので解説は省略しますね
次は 「ReDim GetAly(VsbCel, MaxCol – 1)」の部分です
これは2次元配列の箱の数を指定しています
ReDimは配列の数をあとから指定するための書き方です
その後の「GetAly()」という配列に今からそれぞれの箱の数を指定します
2次元配列とは簡単にいうと2列以上ある範囲の値をPCに覚えてもらう仕組みのことです
細かいことを言うと表現がちょっと違いますが当たらずとも遠からずです
そして「GetAly(VsbCel, MaxCol – 1)」ですがこれを数字で表すと「GetAly(6,1)」となります
「6」という数字はさっき解説した最終行数を調整した結果の数字ですね
次の「1」という数字は1行目つまりタイトル行の最終列数(2列)から配列用に1を引いて調整した数です
とは言っても分かりづらいので、これも図解してみましょう
2次元配列とはこんな感じです
今回は見えている行数つまり「項目の数」は7だったので用意する箱は7つです
ただ、配列の場合「0」から始まるので「0～6」までの7つの箱を用意しました
次は「連番と数字」をセットで配列に格納したい(PCに覚えておいてもらいたい)のでもう1セットいる訳です
なぜなら1つの箱には1つの値しか入れられないので
そうするとMaxColの答えは2でした
でも配列は「0」から始まるので「0～1」で2項目の箱を用意した訳です
これで7行2列分の値を入れておく箱がキレイに準備出来ましたよね
そして2次元配列の書き方は「GetAly(0,0)」という番号の箱に連番の「1」を入れる
続いて「GetAly(0,1)」に数字の「98」を入れる
こうすることで全ての値を全ての箱にキレイに入れることが出来るという訳です
こんな風に設定することで配列の準備が完了しました
Step3 2次元配列に値を格納する
仕上げに用意した2次元配列に値を格納していきましょう
n = 0
m = 0
'配列に表示されたセルの内容を格納する
For i = 1 To MaxRow
If .Rows(i).Hidden = False Then
For j = 1 To MaxCol
GetAly(n, m) = .Cells(i, j).Value
m = m + 1
Next j
n = n + 1
m = 0
End If
Next i
それがこの部分です
For文の解説は今回省略させてもらいます
「For i」の部分は最終行まで処理するという意味です
まず、繰り返し処理で全ての行を対象に処理をしていきます
次のポイントが「If .Rows(i).Hidden = False Then」です
これは「もし、今の行が隠れていないなら」つまり「表示された行なら」という意味です
この処理が無いと「可視セルの値だけ」取得出来ないので重要です
For j = 1 To MaxCol
GetAly(n, m) = .Cells(i, j).Value
m = m + 1
Next j
次のポイントがこの部分です
可視セルだった場合「GetAly(n, m)」という2次元配列に「.Cells(可視セルの行数, 指定された列数)」の値を格納します
nとmの変数は最初「0」に設定されているので「GetAly(0, 0)に最初の可視セルの.Cells(2, 1)の値」が格納されます
次に「m = m + 1」で「mの値は1」になりますね
同時に「.Cells(i, j)」の「j」値がFor文の処理で「1→2」になります
その結果「GetAly(0, 1)になるので可視セルの.Cells(2, 2)の値」が格納されます
n = n + 1
m = 0
格納の処理が終わったら「n = n + 1」で配列を1つ進めます
同時にmを「0」に戻します
つまり「GetAly(0, 1)」は「GetAly(1, 0)」になるという訳です
ちょっと分かりづらいので簡単に図解してみましょう
図解にするとこんな感じですね
図解にしてみるとやっていることは単純なんですが、繰り返しの処理をしようとすると工夫が必要なのである程度経験が必要ですが最初はマネしてみて下さい
これで格納は完了です
ちょっと分かりづらい点はあると思いますが、これが出来ると結構快適になるので最初は大変ですが、チャレンジしてみて下さい
繰り返しになりますが、フィルターで絞り込んだ条件を配列に格納するには可視セルを上手く扱ってデータを格納するのが最適解です
配列に格納されたデータを別のシートに転記する時は配列の最大数を活用するのが最適解です
配列に格納されたデータを別のシートに転記する時は配列の最大値を取得して活用するのがスムースな方法です
なぜなら、配列の最大値は手軽に取得出来るので貼り付けの時に活かすとスムースに貼付け処理が出来ます
では後半では配列に格納した値を別のシートに貼り付けてみましょう
With Worksheets("転記先")
.Cells.Clear
.Cells(1, 1).Resize(UBound(GetAly, 1) + 1, UBound(GetAly, 2) + 1) = GetAly
.Select
End With
貼付けするのがこの部分です
最初の「.Cells.Clear」はセル全体から値を消してリセットしています
まず、貼り付けたい範囲を調整する必要があるので
「 .Cells(1, 1).Resize()」で貼り付ける範囲を設定します
この部分は前半で解説したのでOKですね
次はリサイズしたい範囲を設定します
「UBound(GetAly, 1) + 1」まずは、この部分です
UBound関数は配列の最大値を取得します
「UBound(GetAly, 1)」は配列の1つ目の最大数を取得します
つまり、GetAly(6,1)が一番最後の数字ですよねそして1項目の最大値は左側の数字の「6」です
ただ、「6」は調整した値なので貼り付けたい範囲は「+1」をして元に戻して「7」になります
その結果「7行」というのが決定しました
同じ要領で「UBound(GetAly, 2) + 1」は列数を調整した「1」が一番大きい数字ですよね
ただ、これも列数を配列に合わせて「-1」してあったので「+1」をして2列に直します
その結果「7行2列」の範囲という指定ができました
ここまで出来たらあとはその範囲に配列の内容を貼り付けるだけです
最後の「= GetAly」で配列の中身を反映させれば完成です
こんな感じで可視セルの内容だけを配列に格納して素早く一覧表を作ることが出来ます
このコードを使うと条件を変えても瞬間的に反映してくれるので感動しますよ
繰り返しになりますが、配列に格納されたデータを別のシートに転記する時は配列の最大値を取得して活用するのがスムースな方法です
コメントを残す