AutoFilterプロパティについて

Excel VBAのAutoFilterプロパティについて質問です。
（Excelのバージョンは2010です。）

■背景
・マクロ実行時、オートフィルタを解除し、終了直前に実行前の抽出状態を復元するマクロを組んでいます。

・実行前の抽出情報を取得するために、ワークシートのAutoFilterプロパティから必要な情報を事前に配列に格納しています。
（具体的には、Criteria1、Operator、Criteria2の3つのプロパティをn×3次元の配列に格納）


■問題
・以下の通り、ワークシートにデータが入力されていたとします。
　　A列
　1　10
　2　11
　3　11
　4　11
　5　12

・通常、「トップテン」のオートフィルタで「上位3項目」などのように指定する場合は、以下のプロパティをAutoFilterメソッドに渡します。（細かい記述は割愛）
　　Criteria1:= 3
　　Operator:= xlTop10Items
　※Excelのオートフィルタの仕様上、上述の例で言えば、A1～A4が表示されます。

・このような絞り込みがなされたフィルタ情報を取得しようとした場合、以下の様にAutoFilterプロパティに格納されているため、このまま配列に受け渡した上で、再度オートフィルタを復元しようとしてもエラーとなってしまいます。
　　Criteria1:= ">=11"
　　Operator:= xlTop10Items
　※Operatorに敢えて「0」を受け渡し、「11以上」と指定させるようフィルタを復元することで、
　　 結果的な表示はA1～A4が表示された状態に戻せるのですが、
　　 本来であれば「上位3項目」というフィルタを復元させたいです。

・上記の例の場合、「上位2項目」「上位3項目」「上位4項目」いずれの絞り込みをしたとしても、AutoFilterプロパティには「11以上」という状態しか保持していないため、「上位何項目で絞り込んだか」をRANK関数等から逆算することもできません。

■質問
・「トップテン」のオートフィルタで絞り込みをした場合、「上位何項目で絞り込んだか」を取得するようなプロパティ、または方法（多少無理矢理でも構いません）をご存知でしたら、ご教示頂ければ幸いです。

以上、宜しくお願い致します。

No.1 ベストアンサー 回答者： WindFaller 回答日時： 2014/06/02 22:47

こんばんは。

言われて、なるほどと思いました。それは、不可逆なプロパティなのですね。
要するに、外部ツールを使っているから、取得出来ないのでしょう。

でも、

>「上位何項目で絞り込んだか」
というのは、
>「上位2項目」「上位3項目」「上位4項目」
どれも正解ではないでしょうか？

Win32APIで、Top 10 Filter Dialog Box(bosa_sdm_XL9)の中身を取り出せる人もいるかもしれませんが、今の私は、VBAマクロを忘れつつあるので、この程度です。

計算的に、出すしか方法が見当たりません。

以下の注意は、一番上の「項目名あり(True)」をありにしていますが、なし(False)にすれば、先頭から数えます。1列目の場合、2列目の場合とCriteria1を探すようにしています。ただ、複数の項目に対するものとかは、できませんが、以下の考え方を読んでみてください。テクニックはあっても、難しいことはしていません。また、Excelのバージョンによっても、読み取れる数の制限が発生するかもしれません。


'//
Sub Test1()
Dim Rng As Range
Dim n As Variant
Dim cl As Long
Dim dummy As Variant
Dim j As Long
Dim Ar() As Variant
Dim Arx() As Long
Dim i As Long
Dim c As Range
Dim cnt As Long
Dim t As Long
Dim a As Variant
Const bHEAD As Boolean = True '*注意-項目名あり

With ActiveSheet
'チェック-AutoFilter
　If .AutoFilterMode = False Then MsgBox "オートフィルタモードではありません。", 48: Exit Sub
　If .FilterMode = False Then MsgBox "すべて表示されています。", 48: Exit Sub
　 On Error Resume Next
　 For cl = 1 To .AutoFilter.Filters.Count
　　　dummy = .AutoFilter.Filters(cl).Criteria1
　　　If dummy <> Empty Then Exit For
　　　dummy = Empty
　 Next cl
　 On Error GoTo 0
　If .AutoFilter.Filters(cl).Operator <> xlTop10Items Then MsgBox "上位項目ではありません。", 48: Exit Sub
　　　
　　 Set Rng = .AutoFilter.Range
　　 If bHEAD Then　'トップに項目名があれば、1行減らす
　　　 Set Rng = Rng.Offset(1).Resize(Rng.Rows.Count - 1)
　　 End If
　　 j = Rng.Columns(cl).SpecialCells(xlCellTypeVisible).Count
　　 ReDim Ar(j - 1)
　　 ReDim Arx(j - 1)
　　 For Each c In Rng.Columns(cl).SpecialCells(xlCellTypeVisible)
　　　 If c.Value <> "" Then
　　　 Ar(i) = c.Value
　　　 Arx(i) = i + 1
　　　 i = i + 1
　　　 End If
　　 Next c
　　 Ar = Application.Small(Ar, Arx)　'昇順に並べ替え
　　 For Each a In Ar
　　　 If t <> a Then
　　　　　cnt = cnt + 1
　　　 End If
　　　 t = a
　　 Next a
End With
　 If cnt <> j Then
　　 MsgBox "上位" & cnt & "～" & j & "までの項目", vbInformation
　 Else
　　 MsgBox "上位" & j & "までの項目", vbInformation
　　
　 End If
End Sub
'//

この回答へのお礼

WindFallerさん

とてもご丁寧なご回答、ありがとうございます。

ソースコード、大変参考になりました。
これなら、「上位何項目で絞り込んだか」をかなりの確度で算出できそうです。


>>「上位何項目で絞り込んだか」
>というのは、
>>「上位2項目」「上位3項目」「上位4項目」
>どれも正解ではないでしょうか？
個人的には「上位3項目」で絞り込んでいたのであれば、復元時にも「上位3項目」で設定し直したいイメージでした。
（トップテン オートフィルタのコマンドボックスに「上位」「3」「項目」と出るので、復元前後でここの値が変わってしまうのは避けたい。。）
但し、AutoFilterプロパティの限界のようですね。


＞Win32APIで、Top 10 Filter Dialog Box(bosa_sdm_XL9)の中身を取り出せる人もいるかもしれません
こちらについても、もう少し調べてみようと思います。
アドバイスありがとうございます。


※ベストアンサーについては、もう少々お待ちください。
（他の皆様のご回答もお待ちした上で判断させて頂きたく。）

お礼日時：2014/06/03 12:27