只是为了澄清:
我不想删除重复的行,我想删除行中的重复单元格
这是一个经典的地址表,在某些行中有重复的条目
我需要删除这些条目。
我在 VBA 中看到的大部分内容都是用于删除列中的重复值,但我找不到删除行中的重复值的方法。
Name | Address1 | Address2 | City | Country
Peter | 2 foobar street |2 foobar street | Boston | USA
我希望它是这样的:
Name | Address1 | Address2 | City | Country
Peter | 2 foobar street | | Boston | USA
我编写了一个宏,它将循环遍历所有行,然后循环遍历每行的每一列,但我不知道如何在同一行的不同单元格中发现重复项。
下面是代码:
Sub Removedupe()
Dim LastRow As Long
Dim LastColumn As Long
Dim NextCol As Long
LastRow = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
LastColumn = Cells.Find(What:="*", After:=Range("A1"), SearchOrder:=xlByColumns, SearchDirection:=xlPrevious).Column
For counterRow = 1 To LastRow
'I'm stuck here: how to remove a duplicate values within that row?
Next counterRow
End Sub
也许这会解决你的问题:
Sub RemoveDuplicatesInRow()
Dim lastRow As Long
Dim lastCol As Long
Dim r As Long 'row index
Dim c As Long 'column index
Dim i As Long
With ActiveSheet.UsedRange
lastRow = .Row + .Rows.Count - 1
lastCol = .Column + .Columns.Count - 1
End With
For r = 1 To lastRow
For c = 1 To lastCol
For i = c + 1 To lastCol 'change lastCol to c+2 will remove adjacent duplicates only
If Cells(r, i) <> "" And Cells(r, i) = Cells(r, c) Then
Cells(r, i) = ""
End If
Next i
Next c
Next r
End Sub
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)