VBAリストボックスへの複数列の項目追加について

質問

現在、VBAのリストボックスへの複数列の項目追加に苦戦しております。
まだまだ未熟の為、どうか皆様のお力をお貸し下さい。

手法等、間違えてる箇所があるかと存じますので、ご指摘と、また別に手法がございましたら、ご教授お願い致します。

現在、リストボックスへの複数列の項目追加の為、以下のように記述しておりますが、上手く動きません。
※あらかじめ、UserForm1にリストボックス（ListBox1）と、コマンドボタンを作成しています。
※Sheet1に、添付のように書いております。

Private Sub CommandButton1_Click()
Dim rngTemp1 As Range
Dim rngTemp2 As Range
Dim rngTemp3 As Range

With Sheets("Sheet1")
.Activate
Set rngTemp1 = .Cells(2, 2)
rngTemp1.Select

Set rngTemp2 = .Range(.Cells(2, 4), .Cells(2, 6))
rngTemp2.Select

Set rngTemp3 = Union(rngTemp1, rngTemp2)
rngTemp3.Select
End With

UserForm1.ListBox1.ColumnCount = rngTemp3.Count
UserForm1.ListBox1.List = rngTemp3.Value 'ここでエラー
End Sub

ウォッチ式で見ると、rngTemp3には、rngTemp1の値しか入っておらず、その為にエラーとなっているようです。
が、rngTemp3.Selectの時点では、正常に選択出来ております。

(1)そもそもListプロパティで、離れたセルをリストボックスへ追加するのは無理なのでしょうか？
※Userform1.ListBox1.List=rngTemp2.Valueとすると正常に追加されます。

(2)このような場合を解決する手段は他にありませんでしょうか？(配列の手法などありましたら、教えて下さい。)
※ちなみに実際のデータ列は10列以上ある為、AddItemでは10列目でエラーとなり、無理でした。

環境は、Office2010になります。

説明がわかりづらく申し訳ありませんが、何卒よろしくお願い致します。

回答No.3

watabe007 · ベストアンサー

>ListCountを用いて、その都度行数を取得し、行の最後に追加していく・・・
その都度列数(ColumnCount)が変わることは検証していません
>Union(.Cells(2, 2), .Range(.Cells(2, 4), .Cells(2, 6)))
同じ行リストが追加されるのでは
Dim rngTemp1 As Range, c As Range, v As Variant
Dim i As Long, j As Long, k As Long, n As Long
With Sheets("Sheet1")
　　.Activate
　　Set rngTemp1 = Union(.Cells(2, 2), .Range(.Cells(2, 4), .Cells(2, 6)))
End With
n = rngTemp1.Count
With Me.ListBox1
　　.ColumnCount = n
　　ReDim v(.ListCount, 1 To n)
　　For i = 0 To .ListCount - 1
　　　　For j = 1 To .ColumnCount
　　　　　　v(i, j) = .List(i, j - 1)
　　　　Next
　　Next
　　For Each c In rngTemp1
　　　　k = k + 1
　　　　v(.ListCount, k) = c.Value
　　Next
　　.List = v
End With

お礼

重ねてのご回答、誠にありがとうございました。
説明が分かりづらく申し訳ありません。

watabe007さんのお陰でやりたい事を実現出来ました。

本当にありがとうございました。

回答No.2

watabe007

>配列の手法などありましたら、教えて下さい。
Private Sub CommandButton1_Click()
　　Dim rngTemp1 As Range
　　Dim v As Variant, i As Long, n As Long, c As Range

　　With Sheets("Sheet1")
　　　　.Activate
　　　　Set rngTemp1 = Union(.Cells(2, 2), .Range(.Cells(2, 4), .Cells(2, 6)))
　　End With
　　n = rngTemp1.Count
　　ReDim v(0, n - 1)
　　For Each c In rngTemp1
　　　　v(0, i) = c.Value
　　　　i = i + 1
　　Next
　　With Me.ListBox1
　　　　.ColumnCount = n
　　　　.List = v
　　End With
End Sub

補足

ご助言ありがとうございます。

watabe007さんの仰られた手法で実施した事で可能になりました。

そこで、重ねてで申し訳ありませんが、教えて下さい。

現在、watabe007さんの手法で実施すると、ListBoxに1行追加された後、
続けて追加した場合、そのListBoxの1行目に上書きされてしまいます。

上記コマンドは、クラスにて定義しており、クリックイベントで呼び出しており、
随時、行数を追加して、データを追加したいと思っています。

ListCountを用いて、その都度行数を取得し、行の最後に追加していくのかと思考錯誤しておりますが、
なかなか上手く動いてくれません。
以下のように記述していますが、追加する行のみが更新され、元あった行は消えていきます。

===========================================
　　ReDim v(Me.ListBox1.Count, n - 1)
　　For Each c In rngTemp1
　　　　v(Me.ListBox1.Count, i) = c.Value
　　　　i = i + 1
===========================================

お手数おかけしますが、どうかご教授お願い致します。

回答No.1

watabe007

Cells(2, 2)から5列分、取得してListBoxの２列目を非表示(列幅0)にしました。
Private Sub CommandButton1_Click()
　　Dim rngTemp1 As Range

　　With Sheets("Sheet1")
　　　　.Activate
　　　　Set rngTemp1 = .Cells(2, 2).Resize(, 5)
　　End With
　　With UserForm1.ListBox1
　　　　.ColumnCount = rngTemp1.Count
　　　　.ColumnWidths = ";0"
　　　　.List = rngTemp1.Value
　　End With
End Sub

お礼

ご助言頂き、誠にありがとうございます。

非表示にするやり方で、実現出来る事が確認出来ました。

ただ、今回はwadabe007さんの手法を用いる事に致しました。
（リストボックスに載せたデータをそのままcsvデータとして出力する為。）

また、watabe007さんへ補足で伺っており、もしそちらもお知恵をお貸し頂けましたら幸いです。

誠にありがとうございました。