|
发表于 2014-11-3 17:10
|
显示全部楼层
本楼为最佳答案
- Sub demo()
- Dim arr, arr3(1 To 256, 1 To 12)
- Dim i&, j&, k&, lRow&
- Dim sht As Worksheet
- Dim strPrefix$, lSn&
- '源数据
- arr = Range("a1").CurrentRegion
- Application.ScreenUpdating = False
- Set sht = Worksheets.Add(after:=ActiveSheet)
- lRow = 2
- '标题行
- Range("a1").Resize(, UBound(arr, 2)).Value = WorksheetFunction.Index(arr, 1)
- '每一行原始记录
- For i = 2 To UBound(arr)
- strPrefix = Left(arr(i, 1), 2) '序号前缀
- lSn = Mid(arr(i, 1), 3) '起始序号
- For j = 0 To arr(i, 2) - 1 '重复次数
- For k = 3 To UBound(arr, 2)
- arr3(j + 1, 1) = strPrefix & lSn + j '3-12列数据写入新数组中
- arr3(j + 1, k) = arr(i, k) '新序号
- Next
- Next
- arr3(1, 2) = arr(i, 2) '第一行第2列数量
- If j Then
- '写回到工作表中并设置格式
- With Cells(lRow, 1).Resize(j, UBound(arr3, 2))
- .Value = arr3
- .Borders(xlDiagonalDown).LineStyle = xlNone
- .Borders(xlDiagonalUp).LineStyle = xlNone
- With .Borders
- .LineStyle = xlContinuous
- End With
- .Borders(xlInsideVertical).LineStyle = xlNone
- .Borders(xlInsideHorizontal).LineStyle = xlNone
- End With
- Erase arr3 '数组清空
- lRow = lRow + j '行号
- End If
- Next
- Application.ScreenUpdating = True
- MsgBox "筛选后的结果在 工作表 " & ActiveSheet.Name & " 中"
- ActiveSheet.Previous.Select
- End Sub
复制代码 |
|