How to create an Excel macro to delete duplicate lines

Question

Have someone show an example of Macro in Excel that removes duplicate lines, but comparing all cells of the same, or selecting which compare, example

para linha_atual em todas_as_linhas faça

    para linha_de_comparacao em todas_as_linhas faça
        se todos as células da linha atual forem iguais as células da linha de comparação 
            remova a linha_atual
        fim se
    fim para
fim faça

Or also the same algorithm above but instead of comparing all columns, compare only a list of specific columns

Answer 1

There are two ways to solve this in Excel.

The first is in simple: Using . removeduplicates command from Excel VBA

ThisWorkbook.Sheets("sheet1").Range("A1:B8").RemoveDuplicates Columns:=Array(1, 2), Header:=xlYes

The second way is harder to understand and do: Use two commands for each and check that the values of the objects are equal. Ex:

Dim linha1, linha2 As Object

    For Each linha1 In ThisWorkbook.Sheets("Plan1").Range("A1:A8").Cells

        linha1.Activate

        For Each linha2 In ThisWorkbook.Sheets("Plan1").Range(Selection.Offset(1, 0).Address & ":A8").Cells

            If linha1.Value = linha2.Value Then

                ThisWorkbook.Sheets("Plan1").Range(linha2.Row & ":" & linha2.Row).Delete shift:=xlUp
                linha1.Activate

            End If

        Next linha2

    Next linha1

What I can guarantee is that even the first way to do it is simpler, it’s also safer. Because it may be that in the second format of doing the command for each "jump" a line every time you practice the command . delete

I hope I’ve helped.

At.

Answer 2

Another way to make this exclusion would be with the for loop

Dim linha as double

Dim linha2 as double

Dim valor1 as string 'se for um texto por exemplo o valor a ser buscado

for linha = 2 to 999999 'Começando na linha 2 caso tenha um cabeçalho

   if (len(cells(linha,1)) < 1 then exit for

     colDaVariavel1 = "Inserir o valor da coluna que esta a variavel"

     'Se necessário entre com as colunas das variaveis a serem comparadas em variaveis diferentes e crie variavei como a valor1

    valor1 = cells(linha, colDaVariavel1)

    For linha2 = (linha + 1) to 999999

       if (len(cells(linha2,1)) < 1 then exit for

       if cells(linha2, colunaDaVariavel1) = valor1 and cells(linha2,  colunaDaVariavel2) = valor2 then

          cells(linha2).entireroll.delete

      end if

   next linha2

next linha

Answer 3

Easiest Mode --> Plan1.Cells.Removeduplicates Columns:=1, Header:=xlYes

This way it is only necessary to instantiate the Column and if it has Header or not besides avoiding overloading the Workbook.

Answer 4

I’m learning from VBA now, and I did it this way, maybe simpler:

Dim TESTE As String
Dim TESTE2 As String
Dim LINHAS As String

LINHAS = 0
TESTE = 6
TESTE2 = 7

Range("a5").Select
Do While Plan1.Range("A" + CStr(TESTE)).Value <> ""

If Plan1.Range("t" + CStr(TESTE)).Value = Plan1.Range("t" + CStr(TESTE2)).Value Then

Plan1.Rows(TESTE2).Delete
LINHAS = LINHAS + 1
GoTo Pular
End If

TESTE = TESTE + 1
TESTE2 = TESTE2 + 1
Pular:
Loop

MsgBox "DUPLICADOS REMOVIDOS, FORAM REMOVIDAS " & LINHAS & " LINHAS!", vbInformation

End Sub