Excel VBAのComboboxのRemoveItem

質問

ExcelのVBAでの、すごく初歩的な質問です。Comboboxに読み込まれるリストがシートのA1:A10にあります。ところどころ空白なので、ComboboxのDropDownListにも空白が出でます。この空白を消すために次のようなコードを書いて実行しますがいろいろやってもエラーになってしまいます。どうもRemoveItemのあたりがおかしいようなのですが、どうすればいいのでしょうか？よろしくおねがいします。
ComboBox1.RowSource = "A1:A10"
I = ComboBox1.ListCount - 1
For n = I To 0 Step -1
If ComboBox1.List(n) = "" Then
ComboBox1.RemoveItem (n)
End If
Next

回答No.3

papayuka · ベストアンサー

こんにちは。

ヘルプに「コントロールの RowSource プロパティにデータ ソースが指定されている場合、このメソッドを使っても、リストから行を削除することはできません。」とあるのでダメでしょうね。

元の範囲をソートして使うか、他の方がおっしゃるようにAddItemで必要な物だけ追加するかでしょう。

お礼

なるほど、そういう条件があったんですね。あきらめがつきました（笑）ありがとうございます。

回答No.2

imogasi

下記はワークシートに直接コンボボックスを貼りつけています。
ＵｓｅｒＦｏｒｍには貼りつけていません。後者だと結論は別になるかも知れないと思います。
（１）まず周辺から。ComboBox1.RowSource = "A1:A10" について。
これは実際には、どの様にコーディングされましたか。上のままではエラー
になると思いますが。
（Ａ）Worksheets("sheet1").ComboBox1.RowSource = Range("a1: a10")もダメでした（このメソッドをサポートしていません。）
（Ｂ）私はいつもListFillRangeにＡ１：Ａ１０と入れています。
（Ｃ）Worksheets("sheet1").ComboBox1.RemoveAllItemsもエラーになりました（このメソッドをサポートしていません。）
（Ｄ）ある本に「コンボボックスコントロールにワークシートのデータが設定されている場合は、Ａｄｄｉｔｅｍメソッドが利用できませんとあります。ListFillRangeを使った場合か。
（Ｅ）項目の追加の別方法について
Sub test03()
For i = 1 To 10
Worksheets("sheet1").ComboBox1.AddItem Cells(i, 1)
Next i
End Sub
もダメでした。
（２）項目削除について
４点注意点・心配な点があります。
（Ａ）ListIndexは０から始まるが、Ｆｏｒループはｉ＝１から始めやすい。
（Ｂ）RemoveItem n のn　は第ｎ番目の項目である。ListIndexと１ずれる。
（Ｃ）１つ削除するとループの終わりが１つ手前にずれる。
（Ｄ）１つ削除すると次ぎの削除するListIndexは同じListIndex番号に
なる。
Sub test01()
For i = 0 To 9
Worksheets("sheet1").ListBox1.AddItem Worksheets("sheet1").Cells(i + 1, 1)
Next i
i = Worksheets("sheet1").ComboBox1.ListCount
' MsgBox i
p = 0
For n = 0 To i - 1
' MsgBox Worksheets("sheet1").ComboBox1.List(n)
If Worksheets("sheet1").ComboBox1.List(n) = "" Then
' MsgBox n
MsgBox p
Worksheets("sheet1").ComboBox1.RemoveItem p
i = i - 1
Else
p = p + 1
End If
Next
End Sub
でやりましたがダメでした。
（３）ＶＢ６．０で
Private Sub Form_Load()
l = Array("a", "b", " ", "c", "d", _
"e", " ", "f", "g", " ")
For i = 0 To 9
Form1.Combo1.AddItem l(i)
Next i
i = Form1.Combo1.ListCount
For n = 0 To i - 1
MsgBox n
If Form1.Combo1.List(n) = " " Then
Form1.Combo1.RemoveItem n
Else
End If
Next
End Sub
で上手く行くようです。
「内容なし項目」はスペース１字に変えています。
この場合、（２）の（Ｃ）と（Ｄ）は考えなくて良いことが判りました。
（４）エクセルＶＢＡで
Sub test01()
l = Array("a", "b", " ", "c", "d", _
"e", " ", "f", "g", " ")
For i = 0 To 9
Worksheets("sheet1").ComboBox1.AddItem l(i)
Next i
i = Worksheets("sheet1").ComboBox1.ListCount
MsgBox i
For n = 0 To i - 1
MsgBox n
If Worksheets("sheet1").ComboBox1.List(n) = " " Then
Worksheets("sheet1").ComboBox1.RemoveItem n
Else
End If
Next
End Sub
は上手く行きます。
Worksheets("sheet1").ComboBox1.RemoveAllItemsはエラーになります（
このメソッドをサポートしていません。）
テストで何度も実行すると、項目が累積しますので注意。
結論として、エクセルＶＢＡでワークシート上にコンボボックスを貼りつけると、RemoveIten が使えないのではないでしょうか。
であれば、ListFillRangやDatasourceを使わず、シートのセルＡ１：Ａ１０から自分で、空白分は除いて、、AddItemでセットすることになるでしょう。

お礼

どうも沢山情報をありがとうございます。当方がやってみたのは、UserForm上にComboBoxを張るつける方です。これだと、ComboBox1.RowSource = "A1:A10"はうまくいきました。

回答No.1

bin-chan

「不要なものを消す」ことより、「必要なものを追加」した方が良いのでは？

For文でなら、

For n = 1 To 10
　　Range("A" & n ).value <> "" Then
　　　　ComboBox1.AddItem Range("A" & n ).value
　　End If
Next n

で良かったかな。

お礼

なるほど、発想を転換すれば、あっという間に解決するんですね。RemoveItemが使えないのことにこだわってしまいますが・・・。ありがとうございます。