VBA 转置

Public Sub a()
Dim sr1 As Integer: sr1 = Selection.Item(1).Row
Dim sr2 As Integer: sr2 = 1
Dim i As Long: i = 1
Do While i <= Selection.Count
For j = ColumnStart(Selection) To ColumnEnd(Selection)
If Not IsEmpty(Selection.Item(i)) Then
For k = 1 To Selection.Item(1).Column - 1
Sheet2.Cells(sr2, k) = Sheet1.Cells(sr1, k)
Next k
Sheet2.Cells(sr2, k) = Sheet1.Cells(Selection.Item(1).Row - 1, j)
Sheet2.Cells(sr2, k + 1) = Selection.Item(i).Value
sr2 = sr2 + 1
End If
i = i + 1
Next j
sr1 = sr1 + 1
Loop
MsgBox "完成!"
End Sub
Private Function ColumnCount(ByVal s As Range) As Integer
ColumnCount = s.Columns.Count
End Function
Private Function ColumnStart(ByVal s As Range) As Integer
ColumnStart = s.Item(1).Column
End Function
Private Function ColumnEnd(ByVal s As Range) As Integer
ColumnEnd = ColumnStart(s) + ColumnCount(s) - 1
End Function