VBA Advancedfilterの使用法

Question

やりたい事
１行目は項目行でズラズラ並んでます。２行目以降がデータです。
A列は商品名です。A列で検索をかけ、商品名１、商品名２、、、、、、商品名１２までをA列から探し出し、該当する行全体を別シートにコピーします。現状ではFor Nextループ２重で検索し、とりあえず動作するようになってますが、filterを使った方が早くてすっきりしてると思い、変更したいのです。

ところが、autofilterの検索条件はcriteria1, criteria2と２個まで、３個以上必要な場合はadvancedfilterを使用すると、どこかで読みました。上記の例では１２個ですが、実使用では数十個です。

advancedfilterの使い方がよく分りません。
MSDNより
式 .AdvancedFilter(Action, CriteriaRange, CopyToRange, Unique)
CriteriaRange（検索条件範囲）が不明です。おそらくここにcriteria1,2に相当することを書くのだろうと思いますが、具体的にどう書けばいいのでしょうか？

WindFaller · Accepted Answer

こんばんは。

>A列で検索をかけ、商品名１、商品名２、、、、、、商品名１２
おっしゃるとおりに、商品名1～商品名12(実際はランダムな名前)というのでしたから、Advancedfilterも良いのかもしれませんね。私の記憶では、この検索条件には、バージョンによって違いがあるといいます。記録マクロではうまくはいく可能性は、7割り程度だと思います。

サンプルとして、

列　A～Hまで、
1列目は項目名
データは、A2から、H16まで

検索条件：J列
出力範囲：A30:H30の列

'//
Sub TestMacro01()
Dim Rng As Range
Dim ArData As Variant

Set Rng = Range("A1").CurrentRegion　'データ範囲

Range("A30").CurrentRegion.ClearContents　'抽出範囲の削除
Range("J1").CurrentRegion.ClearContents　'検索条件の削除

'検索値
ArData = Split("商品名1,商品名2,商品名3,商品名4,商品名6", ",")

'J1は、通常は項目名を入れ、次から検索条件を書き出しますが、
'検索値の末尾に数字がある場合は、商品名1* と同じ意味になるので、
'検索値は、このような約束事の書き方になります。理由は不明、xl2002は、それを排除した。
'=A2="商品名1"

'Range("J1").Value = Range("A1").Value '
For i = 0 To UBound(ArData)
　　Cells(i + 2, "J").Formula = "=A2=""" & ArData(i) & """"
Next i
　　 Rng.AdvancedFilter _
　　　Action:=xlFilterCopy, _
　　　CriteriaRange:=Range("J1").Resize(i + 1), _
　　　CopyToRange:=Range("A30").Resize(1, 8), _
　　　Unique:=False
End Sub
'//
このAdvancedFiler は、MS-DOS時代の生き残りです。Version 2002の時に、正しく変更しようとしたきらいがありますが、大混乱をしてしまいました。
http://support.microsoft.com/kb/214070/ja

さて、ところで、もう少し工夫はできないか、と私は思いました。

//A列は商品名です。A列で検索をかけ、商品名１、商品名２、、、、、、商品名１２までをA列から探し出し、該当する行全体を別シートにコピーします。現状ではFor Nextループ２重で検索し、とりあえず動作するようになってます……//

とのこと。これ自体は、加工すれば、よほどの大きさ出ない限りは、問題はないと思います。

以下は、AutoFilter を使った方法
Excelのバージョンで、2003等の下位バージョンでは無理だと思います。

'//
Sub TestAutoFiler()
With ActiveSheet
　.AutoFilterMode = False
　.Range("A1").CurrentRegion.AutoFilter _
　 Field:=1, _
　 Criteria1:=Array("商品名1", "商品名2", "商品名3", "商品名4"), Operator:=xlFilterValues
　 .Range("A1").CurrentRegion.Copy Range("A30")
　 
　 ''フィルタのコピーが、隠れている行までペーストしてしまった時
　 '.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Range("A30")
　 .AutoFilterMode = False 'AutoFilter を解除
End With
End Sub
'//

この書き込みは、「教えて!goo」の改編前にためておいたものです。なお、私は、もっぱら、「教えて!goo」を離れる気持ちにはなれないのは、掲示板スタイルのレイアウトが保たれているからです。

WindFaller · Answer

#1の回答者です。
ちょっとオフトピですみません。

>リニューアルには私も苦労させられました。この質問のOKwave側を試しに見て下さい。面白いことになってますよ。

私のマクロを少し改造して、Webブラウザで「教えて!gooを閲覧」したら、ボタンを押すと、同じ内容のOkWaveに飛び、もう一度ボタンを押すと、「教えて!gooを閲覧」に戻るように作りかえました。

実は、今回、このレスだけは、OkWave側に書こうと思いましたが、登録までとなると、新たなメアドも必要なので断念しました。(この件、問い合わせ中)　このAdvancedFilterは、Excelの前のもので、それだけに思い入れが強いからです。これらのコマンドが生まれた訳というのも聞きました。

まあ、これからが本当の意味での出発点だと思います。OkWaveと教えて!goo、共にダメになるのか、どちらかが生き残るか、Excelなどの書き込みは、どうやら、OkWave側の人が多いようですが、なるほど、レイアウト的に、OkWaveは見にくいというか、教えて!goo は、ページをまたがなければ、一覧が見れますし、ログも、一覧をTextファイルとして取れます。その分だけ有利だとは思いますが。

tom04 · Answer

No.2です！
投稿後気づきました。
No.1さんの後半部分と同じ回答になっていました。
確認せずに投稿してごめんなさいね。

※　gooがリニューアルしたようで、慣れるまで少し時間がかかりそうです。m(_ _)m

tom04 · Answer

こんばんは！

Excel2007以降のバージョンをお使いであれば、↓のような感じでも大丈夫だと思います。

Sub Sample1()
    Range("A1").AutoFilter field:=1, Criteria1:= _
        Array("商品1", "商品2", "商品3", "商品4", "商品5", "商品6", "商品7", "商品8", "商品9", "商品10", "商品11", "商品12"), _
        Operator:=xlFilterValues
End Sub

※　Excel2003までの場合は使っていない列を作業列として、フラグをたて
その列でフィルタを掛ける方法もあります。m(_ _)m

VBA Advancedfilterの使用法

こんばんは。

#1の回答者です。

No.2です！

こんばんは！

似たような質問が見つかりました

このQ&Aを見た人はこんなQ&Aも見ています

関連するカテゴリからQ&Aを探す

このQ&Aを見た人がよく見るQ&A

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング