Move selection to their respective column's bottom in excel vba

Question

If I have data in 5 columns (A-F) and I select different cells within those columns, I'd like a macro that would move the contents of these cells to their respective columns 12th row.

Egs.: A3,B2,B4,C4,D1,D2,F4 The contents of these should go to A12,B12,C12,D12,F12 separated by a ", ".

This almost does the job, but it doesn't work if I select stuff from more than 1 column:

Sub Move()

Dim selectedCells As Range
Dim rng As Range
Dim i As Integer
Dim values() As String
Dim CSV As String

Set selectedCells = Selection
ReDim values(selectedCells.Count - 1)

i = 0
For Each rng In selectedCells
    values(i) = CStr(rng.Value)
  i = i + 1
Next rng

CSV = Join(values, ", ")

Dim vArr
vArr = Split(Selection.Address(True, False), "$")
SC = vArr(0)

Range(SC & "12").Value = CSV
Selection.ClearContents

End Sub

Thanks for the help in advance!

Answer 1

The use of Selection is discouraged, but if you want to take that approach, what about something like this?

Sub Move()

Dim selectedCells As Range
Dim rng As Range
Dim targetRng As Range

Set selectedCells = Selection

For Each rng In selectedCells
    Set targetRng = Cells(12, rng.Column)
    If IsEmpty(targetRng) Then
        targetRng = rng
    Else
        targetRng = targetRng & ", " & rng
    End If
    rng.ClearContents
Next rng

End Sub