Excel, Microsoft Office, VBA

Excel VBA – Delete duplicate rows

2 Comments

The macro below deletes rows with repeated values based on selected column. It’s almost the same as filter default option in Microsoft Excel, but using VBA Code. Copy and Paste the code in a new VBA Module. Fill a worksheet with some sample data, of course, with repeated values. Now, type Alt+F8 to activate and run the macro to see the result.

Public Sub ExcluirLinhasDuplicadas()
 
    Dim Col As Integer
    Dim r As Long
    Dim C As Range
    Dim N As Long
    Dim V As Variant
    Dim Rng As Range
 
    On Error GoTo EndMacro
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
 
    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 1 Step -1
        V = Rng.Cells(r, 1).Value
        If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
            Rng.Rows(r).EntireRow.Delete
            N = N + 1
        End If
    Next r
 
EndMacro:
 
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
 
End Sub

Source:http://vbamacros.blogspot.com/

Comentários

comentários

2 thoughts on “Excel VBA – Delete duplicate rows”

  1. Opa Tomas, muito boa a macro.

    Só uma pergunta, esta macro remove as ultimas linhas repetidas, pois pelo que entendi ela começa a varredura “de baixo para cima”.

    Teria como fazer ao contrario? ou seja, remover as primeiras linhas?

    Trabalho com uma planilha que sempre que atualizada o usuario deveria excluir as linhas correspondentes anteriores, mas isto nem sempre acontece e preciso fazer isto manualmente.

    Grato

Comments are closed.