|
今天讨论编程去除单列中重复的数据,保留唯一值,当然用字典实现简单速度快,用菜单命令删除重复项更简单易得.
发这个帖子,主要讨论VBA编程基础知识,看看有没有更好的算法实现.感兴趣的请顶一下.
我先发两段程序,已经测试,正常运行.代码如下:
- '利用一个数组时间复杂度高,慢
- Sub 去除单列中重复项1()
- Dim arr
- Dim i, j, m, max_row As Long
- t = Timer
- max_row = Range("a1008888").End(xlUp).Row - 1
- arr = WorksheetFunction.Transpose([a2].Resize(max_row, 1))
-
- '查找去除单列中重复值
- For i = 1 To max_row
- If arr(i) = Chr(0) Then Exit For
- For j = max_row To i + 1 Step -1
- If arr(i) = arr(j) Then
- m = j
- Do While m < max_row
- arr(m) = arr(m + 1): m = m + 1
- Loop
- arr(m) = Chr(0) '清空并设置标志
- End If
- Next j
- Next i
-
- Range("c2:c" & max_row).Clear
- Range("c2").Resize(max_row, 1) = WorksheetFunction.Transpose(arr)
- Erase arr
- MsgBox Timer - t
- End Sub
- '利用临时数组中转,空间复杂度高,较快
- Sub 去除单列中重复项2()
- Dim arr, arr_temp()
- Dim i, j, m, g, max_row As Long
- t = Timer
- max_row = Range("a1008888").End(xlUp).Row - 1
- arr = WorksheetFunction.Transpose([a2].Resize(max_row, 1))
-
- '查找单列中重复值并清空
- For i = 1 To max_row
- For j = i + 1 To max_row
- If arr(i) = arr(j) Then
- arr(j) = Chr(0)
- End If
- Next j
- Next i
-
- '将不重复值放入临时数组
- For i = 1 To max_row
- If arr(i) <> Chr(0) Then
- g = g + 1
- ReDim Preserve arr_temp(1 To g)
- arr_temp(g) = arr(i)
- End If
- Next i
-
- Range("e2:e" & max_row).Clear
- Range("e2").Resize(g, 1) = WorksheetFunction.Transpose(arr_temp)
- Erase arr, arr_temp
-
- MsgBox Timer - t
- End Sub
复制代码
|
|