首先，我必須提到我使用的是Excel for Mac，因此任何代碼建議都需要適用于使用 Office 365的Mac。
我有一個包含九列名稱的大型資料集。如果同一行的多列中有相同的名稱，我想洗掉整行
示例資料集：
所以所有這些行都將被洗掉，因為：
Jason出現twice在一行1Jason3連續出現次數2Jason4連續出現次數3Sam出現twice在一行4Fred3連續出現次數5
因此，無論名稱在同一行資料中重復多少次，我都想洗掉整個行。
我的代碼如下。此代碼有效，但會因大資料集而崩潰。我知道必須有一種更快、更有效的方法來撰寫此代碼，以便它可以處理大型資料集。另外，我的代碼太重復了。必須有一種方法使代碼更簡單。無論如何，這是代碼。
'<---- ***** DELETE ANY ROWS WHERE SAME NAME APPEARS TWICE (OR MORE) IN THAT ROW
Sub RemoveDuplicateRows()
Dim Lastrow As Long
Dim Lrow As Long
Lastrow = Range("A" & Rows.Count).End(xlUp).row
For Lrow = Lastrow To 2 Step -1
If Cells(Lrow, "A").Value = Cells(Lrow, "B").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "A").Value = Cells(Lrow, "C").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "A").Value = Cells(Lrow, "D").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "A").Value = Cells(Lrow, "E").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "A").Value = Cells(Lrow, "F").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "A").Value = Cells(Lrow, "G").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "A").Value = Cells(Lrow, "H").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "A").Value = Cells(Lrow, "I").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "B").Value = Cells(Lrow, "C").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "B").Value = Cells(Lrow, "D").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "B").Value = Cells(Lrow, "E").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "B").Value = Cells(Lrow, "F").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "B").Value = Cells(Lrow, "G").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "B").Value = Cells(Lrow, "H").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "B").Value = Cells(Lrow, "I").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "C").Value = Cells(Lrow, "D").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "C").Value = Cells(Lrow, "E").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "C").Value = Cells(Lrow, "F").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "C").Value = Cells(Lrow, "G").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "C").Value = Cells(Lrow, "H").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "C").Value = Cells(Lrow, "I").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "D").Value = Cells(Lrow, "E").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "D").Value = Cells(Lrow, "F").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "D").Value = Cells(Lrow, "G").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "D").Value = Cells(Lrow, "H").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "D").Value = Cells(Lrow, "I").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "E").Value = Cells(Lrow, "F").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "E").Value = Cells(Lrow, "G").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "E").Value = Cells(Lrow, "H").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "E").Value = Cells(Lrow, "I").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "F").Value = Cells(Lrow, "G").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "F").Value = Cells(Lrow, "H").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "F").Value = Cells(Lrow, "I").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "G").Value = Cells(Lrow, "H").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "G").Value = Cells(Lrow, "I").Value Then
Cells(Lrow, "A").EntireRow.Delete
ElseIf Cells(Lrow, "H").Value = Cells(Lrow, "I").Value Then
Cells(Lrow, "A").EntireRow.Delete
End If
Next Lrow
End Sub
uj5u.com熱心網友回復：
假設您的資料如下所示
代碼
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim Ar As Variant
Dim lRow As Long, lCol As Long
Dim i As Long, j As Long, k As Long, l As Long
'~~> Set this to the relevant sheet
Set ws = Sheet1
With ws
If Application.WorksheetFunction.CountA(.Cells) = 0 Then Exit Sub
'~~> Find last row and column
lRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
lCol = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
'~~> Get the data into an array
Ar = .Range(.Cells(1, 1), .Cells(lRow, lCol))
End With
'~~> Clear the rows in an array for the required condition
Application.StatusBar = "Processing Array"
DoEvents
For i = LBound(Ar) To UBound(Ar)
For j = 1 To lCol
For k = 2 To lCol
'~~> An additional check to see if the compared cell is not blank
If Ar(i, j) = Ar(i, k) And Len(Trim(Ar(i, 1))) <> 0 And j <> k Then
For l = 1 To lCol: Ar(i, l) = "": Next l
Exit For
End If
Next k
Next j
Application.StatusBar = "Processing row " & i & " of " & UBound(Ar)
DoEvents
Next i
Dim delRange As Range
With ws
'~~> Clear data for output
.Cells.Clear
'~~> Get the data back in the worksheet
.Range("A1").Resize(lRow, lCol).Value = Ar
If Application.WorksheetFunction.CountA(.Cells) = 0 Then Exit Sub
'~~> Find the new last row
lRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
'~~> Check for blank rows
For i = 1 To lRow
If Application.WorksheetFunction.CountA(.Range(.Cells(i, 1), .Cells(i, lCol))) = 0 Then
If delRange Is Nothing Then
Set delRange = .Rows(i)
Else
Set delRange = Union(delRange, .Rows(i))
End If
End If
Application.StatusBar = "Checking row " & i & " of " & lRow & " for blanks"
DoEvents
Next i
'~~> If blank rows found then delete them in one go
If Not delRange Is Nothing Then delRange.Delete shift:=xlUp
End With
Application.StatusBar = "Ready"
DoEvents
End Sub
在行動
編輯
這是代碼的較慢版本
Option Explicit
Sub Sample()
Dim ws As Worksheet
Dim lRow As Long, lCol As Long
Dim i As Long, j As Long
Dim delRange As Range
'~~> Set this to the relevant sheet
Set ws = Sheet1
With ws
If Application.WorksheetFunction.CountA(.Cells) = 0 Then Exit Sub
'~~> Find last row and column
lRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
lCol = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Column
'~~> Clear the rows in an array for the required condition
Application.StatusBar = "Processing Rows"
DoEvents
For i = 1 To lRow
For j = 1 To lCol
If Len(Trim(.Cells(i, j).Value2)) <> 0 Then
If Application.WorksheetFunction.CountIf(.Rows(i), .Cells(i, j).Value2) > 1 Then
.Rows(i).ClearContents
Exit For
End If
End If
Next j
Application.StatusBar = "Processing row " & i & " of " & lRow
DoEvents
Next i
If Application.WorksheetFunction.CountA(.Cells) = 0 Then Exit Sub
'~~> Find the new last row
lRow = .Cells.Find(What:="*", _
After:=.Range("A1"), _
Lookat:=xlPart, _
LookIn:=xlFormulas, _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, _
MatchCase:=False).Row
'~~> Check for blank rows
For i = 1 To lRow
If Application.WorksheetFunction.CountA(.Range(.Cells(i, 1), .Cells(i, lCol))) = 0 Then
If delRange Is Nothing Then
Set delRange = .Rows(i)
Else
Set delRange = Union(delRange, .Rows(i))
End If
End If
Application.StatusBar = "Checking row " & i & " of " & lRow & " for blanks"
DoEvents
Next i
'~~> If blank rows found then delete them in one go
If Not delRange Is Nothing Then delRange.Delete shift:=xlUp
End With
Application.StatusBar = "Ready"
DoEvents
End Sub
uj5u.com熱心網友回復：
通過一點研究，我發現了以下函式可以洗掉同一單元格中的重復項。
Function RemoveDupeWords(text As String, Optional delimiter As String = " ") As String
Dim dictionary As Object
Dim x, part
Set dictionary = CreateObject("Scripting.Dictionary")
dictionary.CompareMode = vbTextCompare
For Each x In Split(text, delimiter)
part = Trim(x)
If part <> "" And Not dictionary.Exists(part) Then
dictionary.Add part, Nothing
End If
Next
If dictionary.Count > 0 Then
RemoveDupeWords = Join(dictionary.keys, delimiter)
Else
RemoveDupeWords = ""
End If
Set dictionary = Nothing
End Function
在您需要解決方案的范圍內將此函式作為公式應用。
RemoveDupeWords(text, [delimiter])
在哪里：
文本（必需） - 要從中洗掉重復文本的字串或單元格。分隔符（可選）- 分隔重復文本的分隔符。如果省略，則使用空格作為分隔符。該函式不區分大小寫，這意味著小寫和大寫字母被視為相同的字符。
資料來源：AbleBits
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/361421.html
上一篇：回歷日期格式
下一篇：轉到名稱包含字串的最后一個作業表