TEXTJOIN関数

できるだけ楽に県名の配列を用意したい。
ネットで県のリストを探してExcelのA1にコピペ、TEXTJOIN関数で繋げる。

=TEXTJOIN(""",""",TRUE,A1:A47)

'TEXTJOIN関数の結果
北海道","青森県","岩手県","宮城県","秋田県","山形県","福島県","茨城県","栃木県","群馬県","埼玉県","千葉県","東京都","神奈川県","新潟県","富山県","石川県","福井県","山梨県","長野県","岐阜県","静岡県","愛知県","三重県","滋賀県","京都府","大阪府","兵庫県","奈良県","和歌山県","鳥取県","島根県","岡山県","広島県","山口県","徳島県","香川県","愛媛県","高知県","福岡県","佐賀県","長崎県","熊本県","大分県","宮崎県","鹿児島県","沖縄県

最初と最後にダブルクォーテーションを追加してVBAにコピペ。

Sub 配列を用意したい()
    
    Dim myArray
    myArray = Array(ココにコピペ)
    
    Dim i As Integer
    For i = LBound(myArray) To UBound(myArray)
        Debug.Print myArray(i)
    Next
    
End Sub

Splitの逆、Joinなる関数もある。

Debug.Print Join(myArray, ",")

以上。