冬物クリーニング最大49%OFFはコチラ

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

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

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

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

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

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

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

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




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

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

配列を返します。

End Function

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

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

「Excel オートフィルタのリストを取得」の質問画像
教えて!goo グレード

A 回答 (2件)

#1、cjです。

#1補足欄へのレスです。

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

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

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

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

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

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



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

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

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

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

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

回答ありがとうございました!
言葉足らずで質問ばかりの内容を
ひとつひとつ丁寧に回答してくださり、ありがとうございます。
教えて頂いたプログラムで上手く起動したので、
内容を少しずつ理解していこうと思います。
ありがとうございました!

お礼日時:2013/11/08 08:42

こんにちは。



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

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

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

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

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

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

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

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

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


' ' 標準モジュール

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型)は[指定列]以降のリスト列数(省略可。既定値は1。)。
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

この回答への補足

回答ありがとうございます!
色々文章不足で申し訳ないです。

やりたいこととしまして、
指定列の重複部分を削除したリスト(配列変数でも可)
が欲しいのです。

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

田中
佐藤
斉藤
有野
吉田
鈴木
山下
木村
中野
石川
岩下

というデータを配列などに格納したいのです。
また、マクロ(フォーム)間のデータの受け渡し方法が
良く分かりません。
戻り値は1つしか戻せませんし、何か
方法はあるのでしょうか?

補足日時:2013/11/07 13:05
    • good
    • 0

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

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

教えて!goo グレード

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

人気Q&Aランキング