Excel オートフィルタのリストを取得したい

Question

ExcelのVBAです。
オートフィルタの▼をクリックすると、
重複していないリストが出ます。

このリストを列ごと（指定列）取得したいです。

できれば、Functionで組み、
配列に格納し、使いたいと思っています。

戻り値で配列は出来なかった気がしますが、
どうでしょうか…

やりたいことをまとめます。

----------------------

Call　オートフィルタリスト("A")
配列をリストボックスに表示
選択し、上下の配置などを変更できるように
（Excelも連動し、2行目と3行目の位置を変更したりすることができる）

新規追加
そのリスト最終行に新規データを入力
そして、それをExcelにも反映

------------------------
Function　オートフィルタリスト(指定列)
配列定義
指定列の範囲選択

オートフィルタのリストを配列に格納
（重複は削除します）

配列を返します。

End　Function

------------------------------

まとめるどころか余計ぐちゃぐちゃした気もしますが、
回答よろしくお願い致します。

cj_mover · Accepted Answer

＃１、ｃｊです。＃１補足欄へのレスです。

> 指定列の重複部分を削除したリスト（配列変数でも可）
> が欲しいのです。
...
> というデータを配列などに格納したいのです。

Sub Re8336497c()
の、
　　vRtn = オートフィルタリスト(Range(sColRef), 列数)　　'　　複数列を返す場合
'　　vRtn = オートフィルタリスト(Range(sColRef))　　'　　単列を返す場合
の部分、vRtnには配列が格納されている筈ですけれど？
何が違うのでしょうか？

> Call　フィルタ抽出("A")
> などと、A列を指定すれば

Functionプロシージャは関数として値を返すものですから、普通は、
　　戻り値 = フィルタ抽出("A")
のような書式で、（Variant型の）変数に戻り値を格納するものですよね？
＃１で私が書いたものでは、Variant型の変数 vRtn に、お望みのリスト配列が
二次元配列として格納されます。
先にも書きましたが添付画像に合わせて、わざわざ２列のリスト配列を返す
ように書きましたが、単列の場合は
　　vRtn = オートフィルタリスト(Range(sColRef))　　'　　単列を返す場合
こちらの記述を活かして貰えればいいです。
変数を直値に替えて示すと、
　　vRtn = オートフィルタリスト(Range("A:A"))　　'　　単列を返す場合
という内容です。
文字列としての引数ではなくて、Range 型の引数で渡した方が合理的で扱い易い、
ので、引数にはRange 型を指定するだけの違いです。
どうしても、"A"、という記述にに固執するなら、
　　vRtn = オートフィルタリスト(Columns("A"))　　'　　単列を返す場合
とでも、そちらで書き替えて貰えばいいと思いますけれど、、、。

また、変数に格納しなくても、オートフィルタリスト()関数の戻りが配列ですから
そのまま直接、、
　　UserForm1.ListBox1.List = オートフィルタリスト(Range("A:A"))
のように少し雑な書き方でも望み通りに出来ます。

何れにしても、あなたが確認しようともしないだけで、
配列を返す関数を提示して、変数に配列を格納するサンプルを添えて、
配列変数の値をListBoxのListに渡すところまで、テストコードを提示して
テストの仕方まで説明してある、のが、＃１なのですけれど、、、
力及ばず、残念です。

> また、マクロ（フォーム）間のデータの受け渡し方法が
> 良く分かりません。

この説明では、何をお訊ねなのか解りません。
Function()関数に引数を渡して、戻り値を配列で得る、配列をListBoxのListに渡す
という”データの受け渡し方法”の例ならご注文通り示しました。
この他にも知りたい、ということなのでしょうか。
繰り返しになりますが、あれやこれや一度に抱えて迷う位なら、
今目の前にあることを、ひとつずつ、確かめながら、習得するようにしては如何でしょう。

> 戻り値は１つしか戻せませんし、何か
> 方法はあるのでしょうか？

戻り値を複数戻したい、という意味ですかね？
ListBoxの.MultiSelectプロパティを fmMultiSelectMulti で指定してある、
ということでいいですか？
では、例えば、リストの２行め、５行め、８行め、が選択された、として、
「何を契機に」「どんな戻り値を得て」「どんな結果を返したい」のですか？
.Selectedプロパティと.ListIndexプロパティや.Listプロパティについて、
ヘルプに書かれている程度に理解することが出来れば、大抵のことは出来ると思いますけれど、
こちらとしては、注文が無いのに料理を出すことは出来ないですし、残念ですけれど。

せめて、＃１で提示したものを試して、思っていたのとどう違う、とか、
対話的にやりとりが出来れば、そこから汲み取ることは出来たのかも知れません。
とりあえず、私としては既に応えられるものは惜しみなく大盛りでお応えしたつもりなので、
対話的な進展がなければ、今回、書き加えることはないものと思っています。
失礼しました。

cj_mover · Answer

こんにちは。

一度に食べ切れない料理を注文されているように思えたので、
私はメインの一皿分だけ応えてみます。

> オートフィルタの▼をクリックすると、
> 重複していないリストが出ます。
ということですから、単列を指定して単列のリストを返すもの
が欲しいのかと思ったのですが、画像は２列ですね。
例示では[名前]と[選択]がすべて一意の対応になっていますから、
（偶々ではなくて、そういう暗黙の前提があるということ？だと推して）
[指定列]を基準に重複を除いて、複数列返すこともある、ということなのかと考えました。
一応、複数（または単）列、丸ごと、二次元配列で返すように書いてあります。
必要と十分をこちらで理解し切れてませんので、若干欲張りな仕様
でお応えしています。
諸々事情が解れば、多少簡略化できると見込んでいますけれども。。。
但し、要点が霞んでしまわない様にエラー処理を消したものをあげます。
また、日付型のデータについては、そもそものオートフィルタのリストが、
リストボックスに表示できる内容ではありませんから、必然、未対応です。

Function オートフィルタリスト()
が、こちらからのメインの回答に当たります。
　　Sub test8336497c()
は、テスト用のサンプルです。
試す時は、
　　新規のユーザーフォーム＝UserForm1、リストボックス＝ListBox1
　と、サンプルデータのあるシート、の３点
用意してからにした方が紛れがないと思います。。
運用（または実情）に合わせて指定が必要なのはSub test8336497c()の◆４カ所です。

> できれば、Functionで組み、
> 配列に格納し、使いたいと思っています。
> 戻り値で配列は出来なかった気がしますが、
> どうでしょうか…
戻り値をVariant 型にすれば、Function内で宣言した配列変数を代入する形で
実現出来ます。

> Call　オートフィルタリスト("A")
引数[指定列]のこういう指定方法は、Excelには馴染まないかな、と思います。
まだしも"A:A"のようにA1形式のセルアドレスを指定するならアリかも知れませんが、
扱い易さと可読性を重視して[指定列]はRange型にしてあります。
[指定列]には必ず単列を指定するようにしてください。

> 配列をリストボックスに表示
> 選択し、上下の配置などを変更できるように
>（Excelも連動し、2行目と3行目の位置を変更したりすることができる）
>
> 新規追加
> そのリスト最終行に新規データを入力
> そして、それをExcelにも反映
こちらでは、画像と説明された情報を元に実際にサンプルブックを作成してテストしていますが、
（例えば、コマンドボタンなのかスピンボタンなのか、画像からは見分け付かない、とか）
再現できない部分には手が出せないです。

> 配列をリストボックスに表示
この部分だけについて、お応えしていますが、既に十分に厚量になってしまいました。
１段階ずつ、試して、整理して、試して、整理して、、、見通しが付いたら、次の段階、
という風にでも、もう少しスローに進めていった方が、
結果的には早く習得できるようにも思います。
縁があれば、また次の段階でレスすることもあるかも知れませんね。

部分部分はオーソドックスなことしかしていません。
簡単なものには見えないでしょうけれど、それは、不足が無いように
必要以上、十分以上、を書いているからです。

、、、試してみてくださいね。

' ' 標準モジュール

Sub Re8336497c()
　　Dim vRtn As Variant
　　Dim sColRef As String
　　Dim sBuf As String
　　Dim sngLBFontSize As Single
　　Dim 列数 As Long
　　Dim i As Long

sColRef = "B:B"　　'　　要◆指定　[指定列]のアドレスをセル参照（A1形式）文字列で
　　列数 = 2　　'　　option　複数列を返す場合のみ　要◆指定

vRtn = オートフィルタリスト(Range(sColRef), 列数)　　'　　複数列を返す場合
'　　vRtn = オートフィルタリスト(Range(sColRef))　　'　　単列を返す場合

With UserForm1　　'　　要◆指定
　　　　With .ListBox1　　'　　要◆指定
　　　　　　sngLBFontSize = .Font.Size
　　　　　　With Range(sColRef)
　　　　　　　　For i = 1 To UBound(vRtn)
　　　　　　　　　　sBuf = sBuf & ";" & .Cells(1, i).Width * sngLBFontSize / .Cells(3, i).Font.Size '　　フォントサイズ比：リストボックス / セル
　　　　　　　　Next i
　　　　　　End With
　　　　　　.ColumnCount = UBound(vRtn)
　　　　　　.ColumnWidths = Mid(sBuf, 2)
　　　　　　.List = vRtn
　　　　End With
　　　　.Show vbModeless
　　End With
End Sub

' ' /// [指定列]（Range型）を基準に値が重複するセルを除いたリストを二次元配列で返す。
' '　　 [列数]（Long型）は[指定列]以降のリスト列数（省略可。既定値は１。）。
Private Function オートフィルタリスト(ByVal 指定列 As Range, Optional ByVal 列数 As Long = 1)
　　Dim vRtn()
　　Dim Target As Range
　　Dim c As Range
　　Dim cnRc As Long
　　Dim iC As Long
　　Dim iR As Long

With 指定列
　　　　.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
　　　　cnRc = Application.Subtotal(3, .Cells)
　　　　Set Target = .Cells(2, 1).CurrentRegion.SpecialCells(xlCellTypeVisible)
　　　　Set Target = Application.Intersect(Target, .Resize(, 列数))
　　End With

列数 = Target.Columns.Count
　　ReDim vRtn(1 To cnRc, 1 To 列数)

iR = 1: iC = 1
　　For Each c In Target
　　　　vRtn(iR, iC) = c.Text
　　　　If iC Mod 列数 = 0 Then
　　　　　　iR = iR + 1: iC = 1
　　　　Else
　　　　　　iC = iC + 1
　　　　End If
　　Next

指定列.Worksheet.ShowAllData

オートフィルタリスト = vRtn()

Set Target = Nothing: Erase vRtn()
End Function

Excel オートフィルタのリストを取得したい

＃１、ｃｊです。

こんにちは。

この回答への補足

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

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

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

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

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

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