Excelでの入力規則について

質問

Excel2000を使用しています。
Excelでの入力規則で「リスト」を選択し、範囲指定をしてリストを作成したいと考えているのですが、その範囲の中に空白セルが存在するとリストにもその空白が出てきます。
これを表示させないようにするにはどうしたらよいでしょうか？
よろしくお願いします。

回答No.3

imogasi · ベストアンサー

VBAで少しでも短くならないか考えました。
A列に入力規則を設定。
B列をリストとして使う。
E列をワーク列として使う。
そういう例で
B列はB1:B11で(空白行があって）
a
s

d

f
g
h

k
l
VBAはRange("a1:a10").（A1:A10に入力規則を設定するで）
Sub test01()
Range("b65536").Select
Selection.End(xlUp).Select
d = Selection.Row
' MsgBox d
j = 1
For i = 1 To d
If Cells(i, 2) = "" Then
Else
Cells(j, 5) = Cells(i, 2)
j = j + 1
End If
Next i
With Range("a1:a10").Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:="=$E$1:$E$" & Trim(Str(j - 1))
End With
End Sub

お礼

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

回答No.2

nishi6

Ａ　　　　　　Ｂ
１　（入力規則）　　あああ
２　　　　　　　　　いいい
３　　　　　　　　　ううう
４
５　　　　　　　　　おおお

通常の『リスト』設定方法で元の値をセル範囲で指定する方法では難しいでしょう。
実際の値を指定すれば可能なわけで、マクロを使って設定すればデータ数が多くても簡単でしょう。

下のマクロは、
　リストとして使いたいセル範囲（上ではB1:B5）を選択し、コントロールキーを使いながら、
　入力規則を設定するセル（単一セル。上ではA1）を選択して実行します。
　 A1とB1:B5を選択状態にします。
　（この手順は回答行数を減らすためです。他に色々チェックが必要でしょう）
　他のセルに同じ入力規則を設定する必要があれば、A1をコピーし、形式を選択して貼り付けます。

ツール→マクロ→Visual Basic Editor でVBE画面に移り、挿入→標準モジュール で標準モジュールを挿入します。出てきたコードウインドウに下記マクロをコピーして貼り付けます。

Sub myValidation()
　　Dim vadtList As String　　 '// 入力規則リスト
　　Dim setRng As Range　　　　'// 入力規則を設定するセル
　　Dim listArea As Range　　　'// 入力規則リストが登録されているセル範囲
　　Dim rg As Range　　　　　　'// セル
　　Dim rw As Integer　　　　　'// 行カウンタ

　　'// セル選択方法をチェック（これは甘いチェック）
　　If Selection.Areas.Count <> 2 Then MsgBox "No!!!": Exit Sub

　　'// ２つの選択範囲を切り分ける
　　For Each rg In Selection.Areas
　　　　If rg.Count = 1 Then
　　　　　　Set setRng = rg
　　　　Else
　　　　　　Set listArea = rg
　　　　End If
　　Next
　　　　'// 入力規則リストを作る
　　　　With listArea
　　　　　　For rw = 1 To .Rows.Count
　　　　　　　　If .Cells(rw, 1).Value <> "" Then
　　　　　　　　　　vadtList = vadtList & "," & .Cells(rw, 1).Value
　　　　　　　　End If
　　　　　　Next
　　　　　　vadtList = Right(vadtList, Len(vadtList) - 1)
　　　　End With

　　'// 入力規則を設定する
　　setRng.Select
　　With Selection.Validation
　　　　.Delete
　　　　.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
　　　　　　　　　　Operator:=xlBetween, Formula1:=vadtList
　　End With

　　Set setRng = Nothing
　　Set listArea = Nothing

End Sub

お礼

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

回答No.1

noname#9284

「空白」というのは、リストを上から詰めて入力してあって、
入力規則の範囲に指定したよりも入力の行数が少ない
という事で宜しいでしょうか。
（入力規則のリスト範囲を多めに取ってある場合などそうですね。）

可変範囲の名前定義で、リストへの追加／削除に自動的に対応
エクセル技道場　というサイトのページです。
こちらがお役に立つと思います。

参考URL：

http://www2.odn.ne.jp/excel/waza/validation.html#SEC3

お礼

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

補足

空白というのは
Ａ
Ｂ

Ｄ
Ｅ
Ｆ
このようにＣの部分が無いときに範囲指定をするとリストの中にはＣの部分が空白で表示されてしまうということです。