|
描述在附件中,我尝试用数据透视表,但出来的数据不是按原有顺序排的,而且表格中有些没有数据(附件中已用--填充),转换后想保留这些空格,求解!
- Sub 按钮1_Click()
- Dim A, B, i%, j%, s%
- i = Range("a65536").End(xlUp).Row
- A = Range("a1:g" & i)
- ReDim B(1 To (UBound(A) - 1) * (UBound(A, 2) - 1), 1 To 3)
- For j = 2 To UBound(A, 2)
- For i = 2 To UBound(A)
- s = s + 1
- B(s, 1) = A(1, j)
- B(s, 2) = A(i, 1)
- B(s, 3) = A(i, j)
- Next i
- Next j
- Range("j2:l65536").ClearContents
- Range("j:j").NumberFormatLocal = "e-m-d"
- Range("k:l").NumberFormatLocal = "G/通用格式"
- [j2].Resize(UBound(B), UBound(B, 2)) = B
- End Sub
复制代码
转换2.rar
(29.34 KB, 下载次数: 21)
|
|