Excelで参照先のセルを追加してもＯＫのマクロを組みたい

質問

Excelのマクロについて質問です。
参照先のセルを追加してもＯＫのマクロを組みたいのです。

たとえば

_ A
1
2
3
4 あ
5 い
6 ん

という表を作ったとして

Range("A1:A3").Select
Selection.Validation.Add Type:=xlValidateList, Operator:= _
xlBetween, Formula1:="=$A$4:$A$6"

これでセルA1～A3に「あ」「い」「ん」
のいずれかを選択できますよね。
しかし「い」と「ん」の間に新たに「う」「え」「お」
などと追加しても
セルA1～A3に「あ」から「ん」までを参照できるようにしたいのです。

マクロについては
最近になって独学を始めたばかりの初心者です。
先日もこちらで質問させていただいたところ
明確なお答えがあり
とても助かりました。
今回もよろしくお願いいたします。

回答No.1

suz83238 · ベストアンサー

最終行を読み取ればいいわけです。
なるべくSelectionは使わない方がいいです。

Sub Test()
en = ActiveSheet.Cells.SpecialCells(xlLastCell).Row
Range("A1:A3").Validation.Add Type:=xlValidateList, Operator:= _
xlBetween, Formula1:="=$A$4:$A$" & en
End Sub

お礼

おかげさまで、職場でもうまくいきました。
本当にありがとうございました。
これからもよろしくお願いいたします！

補足

早速のお返事、ありがとうございます！
テスト版で試してみたところ、認識しました。
これから職場で、これを実際に適応したいと思います。

ただ一つだけ、結果に大きな影響はないものの

アプリケーション定義またはオブジェクト定義のエラーです。

と表示されます。
これはなぜなのでしょう。
申し訳ありませんが、よろしくお願いします。

回答No.3

Wendy02

こんにちは。

少し、割り込みです。

>アプリケーション定義またはオブジェクト定義のエラーです。
>と表示されます。
>これはなぜなのでしょう

#2さんが、すでに、コードで示されていますが、'Validation' つまり、[入力規則]を設定する場合は、以下のように必ず、'Validation.Delete' を励行しなくてはならないです。

'------------------------------------------
'標準モジュールがベターです。
'------------------------------------------

Sub Test1()
Dim rng As Range
If Range("A4", Range("A4").End(xlDown)).Count > 100 Then
　 MsgBox "データの場所にミスがあるか、セルがひとつしかありません。", vbCritical
　 Exit Sub
Else
　 Set rng = Range("A4", Range("A4").End(xlDown))
End If
With Range("A1:A3")
　.Validation.Delete
　.Validation.Add _
　Type:=xlValidateList, _
　Operator:=xlBetween, _
　Formula1:="=" & rng.Address
End With
End Sub

お礼

Validation.Delete
の励行、よくわかりました。
これまで、なんとなくあるなぁ
とは思っていたのですが。
おかげさまで、うまくいきました。
ありがとうございました。

回答No.2

imogasi

限度数内追加に対してはリストに即座反映する。
例データ　E列
E1:E7
支店
東京
名古屋
大阪
福岡
鹿児島
広島
ーー
質問文感想
・あ、い、う、のような抽象的な例より架空でも、具体名を挙げよ
・入力行の下にリスト範囲を作らないで,別列にリストを作成せよ
入力セルの真下にリストが迫っているのはどうかな。
ーー
標準モジュールに
●多めのセル範囲に名前をつける(E10のところ注意）
Sub test01()
Range("E1:E10").Select
Selection.CreateNames Top:=True, Left:=False, Bottom:=False, Right:= _
False
End Sub
●入力規則の設定
Sub test02()
rng = Range("支店").Address
MsgBox rng
With Range("A1:A11").Validation
.Delete
.Add Type:=xlValidateList, _
Formula1:="=" & rng
End With

End Sub
ーー
やや不満な点もあるが参考に。
ーー
上記コードは色々なテクニックを使っているので
最上行で名前作成
入力規則を範囲命で指定するやり方
など
じっくり勉強をしてください。

お礼

アドバイス、ありがとうございます。
確かに、具体名のほうがわかりやすいですね。
今後、質問するときの参考にさせていただきます。
マクロについても
これからじっくり勉強していくつもりです！