excel - 在excel vba中将选择移动到各自列的底部

Question

如果我在 5 列 (AF) 中有数据并且我在这些列中选择了不同的单元格，我想要一个宏，它将这些单元格的内容移动到它们各自的第 12 行列。

例如：A3,B2,B4,C4,D1,D2,F4 这些内容应该转到 A12,B12,C12,D12,F12 以“,”分隔。

这几乎可以完成这项工作，但如果我从超过 1 列中选择内容，它就不起作用：

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

我在这里先向您的帮助表示感谢！

标签： excelvbaselectionmove

Answer 1

不鼓励使用Selection，但是如果你想采用这种方法，那么这样的东西呢？

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