经常看见用公式和代码比较的文章。看看哪个的速度更加快。那么如何看程序处理的速度,看下图:
详细代码如下:
Option Explicit
Private Sub 清空数据_Click()
Range("A:F,H:ZZ").ClearContents
End Sub
Private Sub 数据转置_Click()
Dim ti As Double
ti = Timer
' 给ti赋值为当前时间
Call sjzz
' 调用数据转置程式
ti = Timer - ti
' 获取运行时间
MsgBox "运行时间为" & ti & "s"
' 弹出对话框
End Sub
Sub sjzz()
Dim value_rows As Integer, value_columns As Byte, a As Integer, b As Byte, i As Integer, f As Integer, x As Byte
Range("H:ZZ").ClearContents
value_rows = Range(Range("A20000"), Range("A20000").End(xlUp)).Row '获取数据最后一行行号
value_columns = Range(Range("G1"), Range("G1").End(xlToLeft)).Column '获取数据最后一列列号
x = Range("G5") '获取数据转置点位数
i = 1
f = 1
For a = 1 To value_rows \ x + 1
For b = 1 To x
Cells(i, 1).Resize(1, x).Copy Cells(f, 9 + (b - 1) * value_columns)
i = i + 1
Next
f = f + 1
Next
End Sub