在 Excel 中将每 3 行移动到一列

在 Excel 中将每 3 行移动到一列

我需要你的帮助。我需要将每 3 行移动到一个新列。假设我有这个:

Ambassade de France
S.E. M. Patrice PAOLI
01-420000-420150

Ambassade de France
Mme.  Jamilé Anan
01-420000-420150

Ambassade de France
Mme . Marie Maamari
01-420000-420150

我需要它们像这样:

Ambassade de France      S.E. M. Patrice PAOLI          01-420000-420150
Ambassade de France      Mme.  Jamilé Anan              01-420000-420150
Ambassade de France      Mme . Marie Maamari            01-420000-420150

我有这个代码。你能帮我吗?它给我错误。超出范围。我应该改变什么?(代码是针对每 7 个,我需要针对每 3 个)

Sub Every7()
    Dim i As Integer, j As Integer, cl As Range
    Dim myarray(100, 6) As Integer 'I don't know what your data is.  Mine is integer data
    'Change 100 to however many rows you have in your original data, divided by seven, round up
    'remember arrays start at zero, so 6 really is 7

    If MsgBox("Is your entire data selected?", vbYesNo, "Data selected?") <> vbYes Then
        MsgBox ("First select all your data")
    End If

    'Read data into array
    For Each cl In Selection.Cells
        Debug.Print cl.Value
        myarray(i, j) = cl.Value
        If j = 6 Then
            i = i + 1
            j = 0
        Else
            j = j + 1
        End If
    Next

    'Now paste the array for your data into a new worksheet
    Worksheets.Add
    Range(Cells(1, 1), Cells(101, 7)) = myarray
End Sub

答案1

尝试这个:

  1. 将数字“6”替换为“2”,并将
  2. 将代码中的数字“7”替换为“3”。

注意:Dim myarray(100, 2)- 此行中的 100 是您拥有的总行数。如果您有(假设)50 行,则将其更改为 50。

相关内容