『VBA　コンボボックスの連動』（佐藤） VBA初心者なのですが、現在使用しているVBAを追加修正したいと思っています。ご教授いただけませんでしょうか。 リストからコンボボックスを使用し、選択したリストの中からさらに項目を選び表示するVBAを使用しています。 -------------------------------------------------------------------- Option Explicit Private Sub ComboBox1_AfterUpdate() End Sub Private Sub ComboBox1_Change() Dim i As Long Dim j As Long Dim var As Variant i = 3 For j = 0 To Me.ListBox1.ListCount - 1 Me.ListBox1.RemoveItem 0 Next Do While Range("AH" & i).Value <> "" If Range("AH" & i).Value = Me.ComboBox1 Then Me.ListBox1.AddItem Range("Ai" & i).Value End If i = i + 1 Loop End Sub Private Sub CommandButton1_Click() Range(RageStr1).Value = Me.ComboBox1.Value Range(RageStr2).Value = Me.ListBox1.Value Unload UserForm1 End Sub Private Sub Label1_Click() End Sub Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean) End Sub Private Sub UserForm_Initialize() Dim i As Long i = 3 Do While Range("AH" & i).Value <> "" Me.ComboBox1.AddItem Range("Ag" & i).Value i = i + 1 Loop End Sub ------------------------------------------------------------------ ★リスト★ AG　　　　　　　AH　　　AI 種類リスト 種類 名前 果物 　　　　果物 マンゴー 野菜 　　　　果物 いちご 　　　　果物 みかん 　　　　果物 びわ 　　　　果物 スイカ 　　　　果物 メロン 　　　　野菜 大根 　　　　野菜 ネギ 　　　　野菜 さつまいも 　　　　野菜 トマト 　　　　野菜 なす 　　　　野菜 かぼちゃ このVBAの連動を1つ増やしたいと思っているのですが、そのリストが 下記のようにAGからAO列にランダムに入力されています。 AG　　　AH　　　AI　　　AJ…　　　AP　　　AQ　　　AR 都道府県 　　　… 　リスト 種類 名前 　　　… 　果物 果物 マンゴー 福岡 長崎 　　　… 　野菜 果物 いちご 福岡 長崎 大分… 果物 みかん 佐賀 長崎 　　　… 　 果物 びわ 福岡 長崎 　　　… 　 果物 スイカ 佐賀 　　　… 　 果物 メロン 佐賀 長崎 大分… 野菜 大根 福岡 佐賀 大分… 野菜 ネギ 福岡 長崎 　　　… 　 野菜 さつまいも 福岡 大分 野菜 トマト 佐賀 　　　… 　 野菜 なす 長崎 大分 野菜 かぼちゃ ---------------------------------------------------------------- 一覧のセルが全て埋まるようにリストを作成できればいいのですが、 他の理由がありこのリストをもとにしなければならず、行き詰っています。 初めて利用するので、質問の仕方もわかりにくく、不慣れなことがあるかとは思いますが、よろしくお願いいたします。 ＜ 使用 Excel：Excel2016、使用 OS：Windows10 ＞ ---- 提示されたリストから、何を辿って、何に行きつきたいか 何パターンか提示すれば、どなたか回答してくれますよ。 現状、？？？です。 （tkit） 2021/05/12(水) 17:13 ---- http://www.excel.studio-kazu.jp/kw/20110209184943.html を、お借りして、解りやすく、表示されると、アドバイス取得率が アップするかも。。。しれません。。。←　多分。。。＾＾；。。。m(__)m （隠居じーさん） 2021/05/12(水) 17:38 ---- >他の理由がありこのリストをもとにしなければならず、行き詰っています。 元のリストを使って、扱いやすいように中間処理のデータを作るマクロも作ってみてはいかがでしょ？ （まっつわん） 2021/05/12(水) 18:01 ---- 既にコメントがあるところですが、私もリスト用の作業表を持つことをお勧めします。 Sub さんぷる() Dim bufRNG As Range Dim dstRNG As Range Dim i As Long With ActiveSheet Set dstRNG = Worksheets.Add.Range("A1") dstRNG.Parent.Name = "作業用" With .Range("AG2:AO" & .Cells(.Rows.Count, "AR").End(xlUp).Row) For i = 1 To .Columns.Count On Error Resume Next Set bufRNG = Nothing Set bufRNG = .Columns(i).SpecialCells(xlCellTypeConstants, 23) On Error GoTo 0 If Not bufRNG Is Nothing Then With Intersect(bufRNG.EntireRow, Union(bufRNG.EntireColumn, bufRNG.Parent.Range("AQ:AR"))) .Copy dstRNG Set dstRNG = dstRNG.Offset(bufRNG.Cells.Count) End With End If Next i End With End With End Sub 上記を実行すると ______AG______AH______AI______AJ___...__ AP______AQ_________AR_______ 1 ****** 都道府県 *************** リスト 種類 名前 2 福岡 長崎 果物 いちご 3 福岡 長崎 大分 果物 みかん 4 佐賀 長崎 果物 びわ 5 福岡 長崎 果物 スイカ 6 佐賀 果物 メロン 7 佐賀 長崎 大分 野菜 大根 8 福岡 佐賀 大分 野菜 ネギ 9 福岡 長崎 野菜 さつまいも 10 福岡 大分 野菜 トマト 11 佐賀 野菜 なす 12 長崎 大分 野菜 かぼちゃ ↓　別シートに出力される ___A_______B_______C__________ 1 福岡 果物 いちご 2 福岡 果物 みかん 3 福岡 果物 スイカ 4 福岡 野菜 ネギ 5 福岡 野菜 さつまいも 6 福岡 野菜 トマト 7 佐賀 果物 びわ 8 佐賀 果物 メロン 9 佐賀 野菜 大根 10 佐賀 野菜 ネギ 11 佐賀 野菜 なす 12 長崎 果物 いちご 13 長崎 果物 みかん 14 長崎 果物 びわ 15 長崎 果物 スイカ 16 長崎 野菜 大根 17 長崎 野菜 さつまいも 18 長崎 野菜 かぼちゃ 19 大分 果物 みかん 20 大分 野菜 大根 21 大分 野菜 ネギ 22 大分 野菜 トマト 23 大分 野菜 かぼちゃ となるので、コンボボックスへの追加はこの作業表を使って、条件に合うものだけ追加すればよいとおもいます。 （もこな２） 2021/05/13(木) 02:55 ---- tkit　様 なにをどう質問すればいいかもわかっていないところ、適格なご指摘ありがとうございます。 コンボボックス1で　AG〜AO列の都道府県を選択する 　↓ コンボボックス2で　種類を選択する 【コンボボックス1で選択した都道府県に該当する種類（果物・野菜）の選択肢のみ表示される】 　↓ リストボックス1で　名前を選択する 【コンボボックス2で選択した種類に該当するもののみ選択肢として表示される】 　↓ 上記にはかいていませんでしたが・・・ リストボックス2で　単価を表示する 【リストボックス1で選択した名前に付随するAS列に記載された単価を表示する】 　↓ CommandButton1で決定する 　↓ コンボボックス2、リストボックス1で選択した文字が入力される と言うことができるようになりたいです。 例としては・・・ 　コンボボックス1→福岡を選択 　コンボボックス2→果物・野菜から　果物を選択 　リストボックス1→いちご、みかん、スイカから　いちごを選択 　リストボックス2→いちごの右となりに入力されている単価が表示される 　エクセルに　「果物」　と「いちご」　と入力される 以上が最終目的です。 （佐藤） 2021/05/13(木) 12:59 ---- 隠居じーさん　様 こんなに便利なVBAを作成してくださっている方がいらっしゃるのですね。 ありがとうございます。 　 少しでも見やすいようにと一生懸命打ったのですが、ガッタガタでしたね・・・泣 （佐藤） 2021/05/13(木) 13:03 ---- まっつわん　様 アドバイスありがとうございます。 やはりリストをまとめた方が良いんですね。 教えて頂きありがとうございます。 （佐藤） 2021/05/13(木) 13:07 ---- もこな２　様 私のわかりにくい文章から、やりたいことを読み取って頂いた上に、 サンプルコードまでご丁寧に教えてくださり、さらにリストの表示も完璧にしてくださって、、、 ありがとうございます。 独学でVBAを使用しており、誰にも聞くことができず本当に困っていたので、 こんなに優しい方たちがいらっしゃることに本当に感動しています！！ 教えて頂いたコードを使ってやってみます。 またわからなければここで引き続き質問させてください。 （佐藤） 2021/05/13(木) 13:13 ---- 引き続き質問させてください。 先日教えて頂いたコードを使用し、5000行ほどの処理を行う事が出来たのですが、 こちらのコードをもう少し高速化できる方法はありますでしょうか？ 自分で調べてみて、「 Application.ScreenUpdating = False」 を書き込めば…と出てきたので試してみましたが、あまり早くなりませんでした。 教えてください。よろしくお願いします。 Sub さんぷる() Dim bufRNG As Range Dim dstRNG As Range Dim i As Long With ActiveSheet Set dstRNG = Worksheets.Add.Range("A1") dstRNG.Parent.Name = "作業用" With .Range("AG2:AO" & .Cells(.Rows.Count, "AR").End(xlUp).Row) For i = 1 To .Columns.Count On Error Resume Next Set bufRNG = Nothing Set bufRNG = .Columns(i).SpecialCells(xlCellTypeConstants, 23) On Error GoTo 0 If Not bufRNG Is Nothing Then With Intersect(bufRNG.EntireRow, Union(bufRNG.EntireColumn, bufRNG.Parent.Range("AQ:AR"))) .Copy dstRNG Set dstRNG = dstRNG.Offset(bufRNG.Cells.Count) End With End If Next i End With End With End Sub （佐藤） 2021/06/02(水) 09:21 ---- ＞こちらのコードをもう少し高速化できる方法はありますでしょうか？ 現在どの位時間がかかっているのですか。 ＞既にコメントがあるところですが、私もリスト用の作業表を持つことをお勧めします。 これは試されたんですか。 （//） 2021/06/02(水) 10:31 ---- 回答ありがとうございます。 現在　5.7秒　程かかっています。 「リスト用の作業表をこのコードで作成する」と認識しているのですが、 私の認識が間違っているのでしょうか… （佐藤） 2021/06/02(水) 10:56 ---- ＞現在　5.7秒　程かかっています。 普通じゃないですか。 ＞「リスト用の作業表をこのコードで作成する」と認識しているのですが、 これとは別の話だと思いますよ。 「別シートに出力される」みたいなものを用意しておくということですね。 これを利用すれば入力してあるデータシートからリストを作成するよりも時間が 少しは短くなるような気がしますけど。リストの変更も容易です。 （//） 2021/06/02(水) 11:46 ---- >普通じゃないですか。 画期的なコードをせっかく教えて頂いたのですが、とても頻繁に使用するので、 もう少し早くできれば…と思い相談させていただきました。 行数が増えると時間がかかってしまうのは仕方ないのですね…。 ＞これとは別の話だと思いますよ。 認識が違い、無視してしまっていたのですね。もうしわけありません。 ご指摘ありがとうございます。 まだ自分の力でコード作成はできそうもないので、これから勉強していきたいと思います。 （佐藤） 2021/06/03(木) 08:38 ---- こんにちは、 5秒も掛かるかしら？ 5000行にして、抽出したら作業用シートは25000行になりましたが 0.1秒掛かったかどうかでしたよ。素晴らしいの一言ですね。 「 Application.ScreenUpdating = False」も使用していませんよ。 佐藤さんが使用しているテーブルは、計算式が重たいのでしょうか？ 文字列だけの抽出なので...基本は速いと思いますが？ と気になる事が数点ほど 佐藤さんがおっしゃる下記内容ですが 例としては・・・ 　コンボボックス1→福岡を選択 　コンボボックス2→果物・野菜から　果物を選択 　リストボックス1→いちご、みかん、スイカから　いちごを選択 　リストボックス2→いちごの右となりに入力されている単価が表示される 　エクセルに　「果物」　と「いちご」　と入力される 以上が最終目的です。 1.テーブルの一覧表に「果物」、「いちご」等と記入されているのに 入力させるとは？何の為なんでしょうか？ テーブル一覧から、出荷伝票(納品書？)を作成したいのでしょうか？ 2.抽出すると、約25,000行程になるのでスタッフさんなどへ 鹿児島行きは○○を数ケースとか、1時間に何回も指示する もしくは、出荷数の確認をご自身でするのであれば... コンボボックスとAutoFilter Fieldを併用するのが一番良いかと思いますよ。 （瞬間でしたよ!） 2021/06/03(木) 16:06 ---- こんにちは。 コメントありがとうございます。 素晴らしいコードを作成して頂いたのに「もっと早く…」 なんて…私の知識がないだけなのに、失礼すぎますよね、ごめんなさい。 ＞使用しているテーブルは、計算式が重たいのでしょうか？ テーブルには計算式は入っておらず、文字列だけです。 使用しているファイル自体にも、たいした計算式は使用していません。 なにか他に遅くなってしまう原因があるということですよね… ＞1.テーブルの一覧表に「果物」、「いちご」等と記入されているのに入力させるとは？何の為なんでしょうか？ 実際使用するテーブルにはもっと選択肢の種類があり、入力する人がたくさんある選択肢の中から、 内容にあったものを絞り込んで入力できるようにしたいと思っています。 ＞2.指示する もしくは、出荷数の確認をご自身でする 使用するのは他の人で、不特定多数が使用します。 コンボボックスとAutoFilter Fieldを併用する方法も調べてみます。 業務内容が含まれるので、都道府県や食べ物の名前で書かせていただいていました。 実際の使用内容や目的が不明瞭にもかかわらず、親身になってお答えいただきほんとうにありがとうございます。 （佐藤） 2021/06/04(金) 09:13 ---- 何度もすみません。 データ元となるセル以外の全て（文字・オブジェクト等）を削除して試してみても、 3〜4秒ほどかかってしまいます。 何か対策で考えられるものがあれば教えて頂けませんでしょうか。 よろしくお願いいたします。 （佐藤） 2021/06/07(月) 16:22 ---- ちょっと確認。 時間がかかって困ってるのはどの部分ですか？ まさか、コンボ（リスト）ボックスを変更する度に作業表を作りなおしているわけじゃないですよね？ （もこな２） 2021/06/07(月) 19:29 ---- もこな2　様 コメントありがとうございます。 ＞時間がかかって困ってるのはどの部分ですか？ →作業表を作成する部分です コンボボックスを変更するたびに作業表を作り直す必要はないのですが、 元となるリストが変更されるタイミングが不特定なため、 ・コンボボックスを変更する度 ・ファイルを開く度 以上2つのいずれかのタイミングで作業表を作るコードを実行したいと思っています。 （新しいシートに作業表を作成する部分を、少し書き換えて使わせていただいています。） なので先日コメント頂いたように0.1秒程になれば…と思い質問させていただきました。 （佐藤） 2021/06/08(火) 09:11