VBA 入力規則で可視セルの範囲は指定できませんか？

With Range("A1").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Sheet2!$C$2:$C$14"
End With

この
Formula1:=
で、SpecialCells(xlCellTypeVisible) のような範囲指定はできないでしょうか？

For nextで可視セル判定する方法で対処はできますが、
ポンとできる方法はないかなと思いまして。

No.2 ベストアンサー 回答者： Qchan1962 回答日時： 2021/08/26 18:10

こんにちは、

Formula1:=で、SpecialCells(xlCellTypeVisible)は試されたと思います。

何処までが簡単と思うのか、わかりませんが、リストを作り登録する方法が簡単だと思います。

リストの作り方は色々方法があります、悩ましい所ではすね。
（悩ましい）色々と言うのは、重複を省く必要を想像できるからですが

サンプルは、SpecialCells(xlCellTypeVisible)なので重複データが有っても
入力規則に登録されます。

入力規則なのだから重複は困る、、と思いますが
その方法は、Dictionaryオブジェクト や　Collectionオブジェクト　などで
調べてください。

Sub sample()
Dim Rng As Range, SC As Range
Dim ListArray(), i As Long, strList As String
With Sheet2
Set Rng = .Range(.Cells(2, "C"), .Cells(14, "C")).SpecialCells(xlCellTypeVisible)
End With

ReDim ListArray(Rng.Count)
For Each SC In Rng
ListArray(i) = SC.Value
i = i + 1
Next
'配列を,区切りの文字列に加工（一番右の,を除く）
strList = Left(Join(ListArray, ","), Len(Join(ListArray, ",")) - 1)

With ActiveSheet.Range("A1")
.Value = ""
.Validation.Delete
.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=strList
End With

End Sub

この回答へのお礼

ありがとうございます。
やっぱりポンっとは出来ないですか。
教えて頂いた内容、参考になります！

お礼日時：2021/08/26 19:35

No.1 回答者： くんこば 回答日時： 2021/08/26 09:20

目的は何ですか？

この回答へのお礼

手法を知るための質問です。
VBAで入力規則を設定する際に、
入力候補の範囲に可視セルのみを簡単に指定する方法はないか、
としか説明できません。

言葉で説明しづらいので、
Validation の、
Formula1:= で、
SpecialCells(xlCellTypeVisible)
のような可視セルに絞り込んだ範囲指定で候補を設定したい
という処理イメージを書く事でわかってもらえると思いました。

ほぼ質問に書いた内容と変わらないですが、
補足として求めておられる「目的」として不足であれば、
もう少し具体的に指摘お願いします。

自分としては十分伝わるだろうと思ってしまっているので、何が不足なのか気づけません。

お礼日時：2021/08/26 10:42