excel吧 关注：281,116贴子：1,547,076
- 35回复贴，共1页
如图，想把右边的数据换成1列展示，感觉可以往offset方向弄，但是弄了半天都没弄懂，所以来问下，请吧友们、大佬们指导下
菠萝蜜 行列不限
Option Explicit
Sub 逆转置()
Dim a, i, j, m
With Sheets("粘贴")
a = [a1].CurrentRegion
ReDim b(1 To (UBound(a) - 1) * (UBound(a, 2) - 4), 1 To 3)
For j = 5 To UBound(a, 2)
For i = 2 To UBound(a)
m = m + 1
b(m, 1) = a(i, 1): b(m, 2) = a(1, j): b(m, 3) = a(i, j)
Next
Next
.[a1].Offset(, UBound(a, 2) + 2).Resize(UBound(b), 3) = b
End With
End Sub
Option Explicit
Sub 逆转置()
Dim a, i, j, m
With Sheets("粘贴")
a = [a1].CurrentRegion
ReDim b(1 To (UBound(a) - 1) * (UBound(a, 2) - 4), 1 To 3)
For j = 5 To UBound(a, 2)
For i = 2 To UBound(a)
m = m + 1
b(m, 1) = a(i, 1): b(m, 2) = a(1, j): b(m, 3) = a(i, j)
Next
Next
.[a1].Offset(, UBound(a, 2) + 2).Resize(UBound(b), 3) = b
End With
End Sub
g啦啦啦，感谢各位大佬、热心吧友们的回复和指导，搞定啦，用的是逆透视的方法，其他方法等完成了工作再来研究研究
