請問excel vba 轉置多行轉單列
轉置多行轉單列
Range("J4:AI4").Select Selection.Copy Range("CN4:CN29").Select Selection.PasteSpecial
-
Range("J6:AI6").Select Selection.Copy Range("CN56:CN81").Select Selection.PasteSpecial
以下公式需運行的時間太久
請問公式該如何簡化呢?
Sub RangeTest()
Range("J4:AI4").Select
Selection.Copy
Range("CN4:CN29").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("J5:AI5").Select
Selection.Copy
Range("CN30:CN55").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("J6:AI6").Select
Selection.Copy
Range("CN56:CN81").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub
根據微軟自己的說明（避免單線程函數），如果不是大量操作的話，個人會建議你直接對每一列使用 TRANSPOSE函數以將其轉置為欄。
如果是大量同格式文件的操作，可以考慮以Sub來對特定目標儲存格寫入TRANSPOSE函數。
如果是在大量格式非固定的文件中操作，則可考慮參考微軟的文件（使用更快的VBA使用者定義函數）撰寫自定義函數。
下面的Function只是概念驗證...我不使用微軟，沒有office，所以未實際測試可用性...本來想在函數裡直接調用TRANSPOSE函數來組合...但是微軟的參考文件實在寫的不夠清楚...
CN4 =UTRANSPOSE("Sheet1", J4:AI6)
Function UTRANSPOSE(ws_source As Worksheet, rn_source As Range) As Variant
Dim Source, vArr() As Variant
Dim i, j, r, c As Long
Dim n As Double
Source = ws_source.rn_source.Value2
r = UBound(Source, 1)
c = UBound(Source, 2)
ReDim vArr(1 To r * c, 1 To 1)
For i = 1 To r
For j = 1 To c
n = n + 1
vArr(n, 1) = Source(i, j)
Next j
Next i
UTRANSPOSE = vArr()
End Function
最後，你可以參考建立更快的VBA巨集的第一段關閉非必要功能來提高VBA運行效能...
Clark741224 wrote:
以下公式需運行的時間太久
不要用select、copy、paste、Transpose
改用陣列
Sub test()
Dim Source, temp() As Variant, i As Integer, j As Integer, r As Integer, c As Integer, n As Double
Source = Sheets("sheet1").Range("a4:n8")
r = UBound(Source, 1)
c = UBound(Source, 2)
ReDim temp(1 To r * c, 1 To 1)
For i = 1 To r
For j = 1 To c
n = n + 1
temp(n, 1) = Source(i, j)
Next j
Next i
Sheets("sheet1").Range("a15").Resize(UBound(temp)) = ""
Sheets("sheet1").Range("a15").Resize(UBound(temp)) = temp()
End Sub
內文搜尋
從 APP 打開
X