miniguy
2003-09-29, 11:45 PM
如果想篩選出重複的數據,應該怎樣做?
例如想篩選出重複的電話號碼的數據等等
謝謝回應!!!
贊助商連結
例如想篩選出重複的電話號碼的數據等等
謝謝回應!!!
贊助商連結
贊助商連結 miniguy 2003-09-29, 11:45 PM 如果想篩選出重複的數據,應該怎樣做? 例如想篩選出重複的電話號碼的數據等等 謝謝回應!!! 贊助商連結 ICLA 2003-09-30, 02:35 AM 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 2003-09-30, 01:03 PM 我也提供一個做參考 *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.asp?CurNumber=284177 miniguy 2003-09-30, 04:31 PM 根據大家提供的方法,已解決了問題 謝謝大家幫忙 ;) ;) ;) |
|