Join with comma and Copy


エクセルで複数カラムの値をカンマ区切りの文字列に変換する方法… - 人力検索はてな

堪え性がないなあ (´・ω・`)

Function join(target, separator)
    txt = ""
    not_first = False

    For Each cell In target
        If not_first Then
            txt = txt & separator
        End If
        txt = txt & cell.Text
        not_first = True

    join = txt

End Function

Sub join_and_copy()
    joined_text = join(Selection, ",")
    Debug.Print (joined_text)

    ' Late Binding to MSForms.DataObject
    Set dataObject = CreateObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")

    dataObject.SetText joined_text

    Set dataObject = Nothing

End Sub

後は Application.MacroOptions か Application.OnKey でショートカットキーの設定をすれば良いという感じ。