删除一行中的重复单元格 [英] Remove Duplicate Cells in a Row

问题描述

只是为了澄清：
我不想删除重复的行，我想删除一行中的重复单元格

Just to clarify : I don't want to remove duplicates rows, I want to remove Duplicate Cells within a row

所以这里是一个经典的地址表，在某些行中有重复的条目
我需要删除这些条目。
我在VBA中看到的大部分用于删除列中的重复值，但我找不到删除一行中重复值的方法。

So here's a classic address table, and in some row there's duplicate entries I need to remove those entries. Most of what I've seen in VBA is used to remove duplicates values within a column, but I can't find a way to remove duplicate values within a row.

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

我写了一个宏，将循环遍历所有行，然后每行的每列，但是我不知道如何在同一行内的不同单元格内找到重复。

I've write a macro that will loop through all the rows and then every columns for each rows, but I have no clue as to how to spot duplicate within teh different cells within teh same row.

这里是以下代码：

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

推荐答案

也许这将解决你的问题：

Maybe this will solve your problem:

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

这篇关于删除一行中的重复单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！