VBA 排序和排列字段

Question

我在MS-Excel中有以下数据：

No1 ABC    
No2 DEF HJK 
No3 HIJ XYZ FGH   
No4 KLM

如何使用 vba 将其排序为以下内容：

No1 ABC    
No2 DEF 
No2 HJK    
No3 HIJ
No3 XYZ
No3 FGH
No4 KLM

Answer 1

见下文，更新源 - 范围（"A1"）和位置 - 范围（"F1"） 或根据需要动态化：

Dim data() As Variant
Dim i As Double, j As Double
Dim rowOffset
Dim result As Variant
Dim results As New Collection
data = Range("A1").CurrentRegion
For i = 1 To UBound(data, 1)
    For j = 2 To UBound(data, 2)
        If (Trim(data(i, j)) <> vbNullString) Then
            results.Add (data(i, 1) & "|" & data(i, j))
        End If
    Next j
Next i
For Each result In results
    With Range("F1")
        .Offset(rowOffset, 0).Value = Split(result, "|")(0)
        .Offset(rowOffset, 1).Value = Split(result, "|")(1)
    End With
    rowOffset = rowOffset + 1
Next result

Answer 2

第一

列中逐行移动，然后为该行（Row）应用填充的其他列数量的循环。因此，2 个循环应该可以解决问题，如下所示：

Private Sub AAA()
    Dim rColumn1 As Range
    Dim rValue As Range
    Dim rTarget As Range
    Set rColumn1 = Range("A1") 'assuming your data set starts in cell A1
    Set rTarget = Range("Q1") 'assuming you want the results in columns Q and R
    Do Until IsEmpty(rColumn1.Value2)
        Set rValue = rColumn1.Offset(0, 1)
        Do Until IsEmpty(rValue.Value2)
            rTarget.Cells(1, 1).Value2 = rColumn1.Value2
            rTarget.Cells(1, 2).Value2 = rValue.Value2
            Set rTarget = rTarget.Offset(1, 0)
            Set rValue = rValue.Offset(0, 1)
        Loop
        Set rColumn1 = rColumn1.Offset(1, 0)
    Loop
End Sub