答案1
这虚拟专用网络代码应该可以工作:
Public Sub summary()
Dim wk As Workbook
Dim ws, ws1 As Worksheet
Set wk = ThisWorkbook
Set ws = wk.Sheets("Sheet1")
Set ws1 = wk.Sheets("Sheet2")
ws1Columns = 1
ws1Rows = 1
maxColumns = ws.Cells(1, Columns.Count).End(xlToLeft).Column
For i = 1 To maxColumns Step 2
theRows = 1
theCell = ws.Cells(theRows, i)
theCell2 = ws.Cells(theRows, i + 1)
While theCell <> ""
If theCell2 <> "" Then
ws1.Cells(ws1Rows, ws1Columns) = theCell
ws1.Cells(ws1Rows, ws1Columns + 1) = theCell2
ws.Cells(theRows, i).Copy
ws1.Cells(ws1Rows, ws1Columns).PasteSpecial Paste:=xlPasteFormats
ws.Cells(theRows, i + 1).Copy
ws1.Cells(ws1Rows, ws1Columns + 1).PasteSpecial Paste:=xlPasteFormats
ws1Rows = ws1Rows + 1
End If
theRows = theRows + 1
theCell = ws.Cells(theRows, i)
theCell2 = ws.Cells(theRows, i + 1)
Wend
Next i
End Sub
使用 ALT+F11 打开 VBA/宏,在本工作簿添加新的模块并粘贴此代码。
执行宏,如果原始数据在,Sheet1
则最终结果将在Sheet2
。