Excel 篩選問題

[miniguy]

如果想篩選出重複的數據,應該怎樣做?

例如想篩選出重複的電話號碼的數據等等

謝謝回應!!!

[ICLA]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'篩選功能沒法篩出重複的項目。我自己是撰寫VBA來找出重複項目。下列程式碼可搜尋第一欄，並將每列的出現次數大於 1 的次數寫在第四欄。第一列會假設為標題列。

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim W As Variant
Dim Rng As Range

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 2 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Cells(r, 4) = Application.WorksheetFunction.CountIf(Rng.Columns(1), V)
End If
Next r
End Sub

[leonchou]

我也提供一個做參考
*make a backup before running this macro - my suggestion..

Sub find_duplicate()
With Columns(1): r = 2: c = [a1].End(xlToRight).Column
Do While Cells(r, 1) <> ""
addr1 = .Find(Cells(r, 1), , , xlWhole).Address
Set f = .FindNext(Cells(r, 1))
Do While f.Address <> addr1
a = True: r1 = f.Row
For i = 1 To c
If Cells(r1, i) <> Cells(r, i) Then a = False
Next i: If a Then Rows(r1).Delete
Set f = .FindNext(Cells(r1 - 1, 1))
Loop: r = r + 1
Loop: End With
End Sub

this macro will find out duplicate in column 1,
then compare rows column by column.
that will save time when the data is huge.

原文出處--
http://qa.usernet.com.tw/total_view....rNumber=284177

[miniguy]

根據大家提供的方法,已解決了問題

謝謝大家幫忙