vba - 从列表中复制元素“n”次，EXCEL VBA

Question

我对 Vba 真的很陌生，所以我尝试学习一些以减少我的工作重复性

我有一个这种格式的列表，我有大约 550 行

Name        N
AAAAA       2
BBBB        5
CCCCC       7

我需要复制每个名称 n 次，像这样

AAAAA    
AAAAA    
BBBBB    
BBBBB    
BBBBB    
BBBBB    
BBBBB

直到姓氏，应该是大约 1500 行

找不到任何方法来做到这一点。请帮忙

提前致谢

标签： vbaexcel

Answer 1

这个简单的代码可以做到：

Sub expand()

    With Worksheets("Sheet2") 'Source sheet
        Dim rng As Variant
        rng = .Range(.Cells(2, 1), .Cells(.Rows.Count, 2).End(xlUp)).Value

        Dim oRng() As Variant
        ReDim oRng(1 To Application.Sum(Application.Index(rng, 0, 2)), 1 To 1)
    End With

    Dim i As Long
    Dim k As Long
    k = 1
    For i = 1 To UBound(rng, 1)
        Dim j As Long
        For j = 1 To rng(i, 2)
            oRng(k, 1) = rng(i, 1)
            k = k + 1
        Next j
    Next i

    With Worksheets("Sheet2").Range("C1") 'Ouput range
        .Resize(UBound(oRng, 1), 1).Value = oRng
    End With



End Sub