[SOLVED] Taking the value of multiple active cells under a column?

Taking the value of multiple active cells under a column?

Is there a way that I can take values of multiple active cells within a column?

Let's say for example I highlight 3 rows and use a macro (Excel VBA) that copies the data in some columns, the string of texts/numbers within the 3 cells under column J (that might be all different values) will all be selected to be copied and pasted later on with this format:

受付番号：JP123456789　PH123456789　MY123456789

Solution

For that I can suggest following routine (comments inline):

Sub CopySelectedColumnJValues()
    Dim cell As Range
    Dim selectedValues As String
    Dim prefix As String
    prefix = "prfx:"

    ' Loop through selected cells and
    ' extract those in column J
    For Each cell In Selection
        ' Only consider cells in column J
        If cell.Column = 10 Then ' Column J = 10
            If Trim(cell.Value) <> "" Then
                selectedValues = selectedValues & Trim(cell.Value) & " "
            End If
        End If
    Next cell

    ' Remove trailing space
    If Right(selectedValues, 1) = " " Then
        selectedValues = Left(selectedValues, Len(selectedValues) - 1)
    End If

    ' Final formatted string
    Dim finalOutput As String
    finalOutput = prefix & selectedValues

    ' Display in a message box (or handle differently if you prefer)
    MsgBox finalOutput, vbInformation

    ' Optional: Copy to clipboard
    With CreateObject("new:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") 'MsForms.DataObject
        .SetText finalOutput
        .PutInClipboard
    End With
End Sub

Part ' Optional: Copy to clipboard was based on this SO post (upvotes due!).