How to create an Excel macro to delete duplicate rows

Navigation
2

You can show someone an example of Macro in Excel that removes duplicate rows, but comparing all rows in the same cell, or selecting which rows to compare, eg 

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

    
asked by anonymous 29.08.2014 / 17:19

3 answers

5

There are two ways to solve this in Excel.

The first is simple : Using the Excel VBA .removeduplicates command 

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

The second way is harder to understand and do : Use two for each commands and verify that the values of the objects are the same. 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 of doing it is simpler, it is also safer. Because it might be that in the second format of making the for each command "skip" a line every time you practice the .delete

I hope I have helped.

At.

    
19.01.2015 / 12:05
2

Another way to do 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
    
30.06.2016 / 18:07
2

The Easy Way - > Plan1.Cells.RemoveDuplicates Columns: = 1, Header: = xlYes

In this way, it is only necessary to instantiate the Column and whether it has Header or not in addition to avoiding overloading of the Workbook.

    
02.02.2017 / 13:31
BBCode Parser ignore what's inside [code] Automatically click when opening site