Excel как удалить повторяющиеся строки макрос

How to delete duplicate values from a data set in all versions of Excel. This includes Excel 2003 and earlier and also Excel 2007 and later.

For Excel 2007 and later, we can use a simple feature within the program, but for Excel 2003 and earlier, we must use a macro, which is provided below.

Sections:

Remove Duplicates in Excel 2007 and Later Versions

Remove Duplicates in Excel 2003 and Earlier Versions

Notes

Remove Duplicates in Excel 2007 and Later Versions

1. Select a cell within the data set that contains the duplicates:
   
2. Go to the Data tab and then click Remove Duplicates:
   
3. You should now see a small window open.
   (Also, Excel should have selected the entire data set, in our example, A1:B6; if it did not select the correct data set, then you need to hit the Cancel button and select the entire data set yourself before clicking the Remove Duplicates button.)
   In the small window, you need to select which column you want to check for duplicates.  You can select one column to check or multiple columns.
   If there are headers or titles for your data and those were also selected by Excel, then click the checkbox next to where it says My data has headers.
   
   In this example, my data does not have headers and I only want to search Column A for duplicates, so I uncheck Column B and then hit OK.
   
4. Excel will remove the duplicates and you will see a window telling you how many duplicates were removed.
   
5. Here is the final result:
   

Notice that Excel removed the entire row of data and not just the data in Column A.

If you had selected Column A and Column B in Step 3, then no value would have been removed.  This is because, Excel looks at all the values in all the selected columns to see if there are duplicates across them instead of individually in each column.  That is important if you want to perform more complex duplicate removal in Excel.

If you made an error, hit Ctrl + Z on the keyboard to undo the duplicate removal and start over.

Remove Duplicates in Excel 2003 and Earlier Versions

To remove duplicate values from Excel 2003 and earlier versions, we need to use a Macro.

Here are the steps to install and use the macro:

1. When in Excel, hit Alt + F11 to go to the VBA Editor window.
   
2. Go to Insert > Module
   
   
3. You should now see a small window open and that is where we will input the macro:
   
4. In that window, copy and paste this macro:

   Public Sub DeleteDuplicateRows()
   ' This macro will delete all duplicate rows which reside under
   ' the first occurrence of the row.
   '
   'Use the macro by selecting a column to check for duplicates
   'and then run the macro and all duplicates will be deleted, leaving
   'the first occurrence only.
   
   Dim R As Long
   Dim N As Long
   Dim V As Variant
   Dim Rng As Range
   
   On Error GoTo EndMacro
   
   Application.ScreenUpdating = False
   Application.Calculation = xlCalculationManual
   
   Set Rng = Application.Intersect(ActiveSheet.UsedRange, _
                       ActiveSheet.Columns(ActiveCell.Column))
   
   Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")
   
   N = 0
   
   For R = Rng.Rows.Count To 2 Step -1
   
       If R Mod 500 = 0 Then
       
           Application.StatusBar = "Processing Row: " & Format(R, "#,##0")
       
       End If
       
       V = Rng.Cells(R, 1).Value
       
       If V = vbNullString Then
       
           If Application.WorksheetFunction.CountIf(Rng.Columns(1), vbNullString) > 1 Then
       
               Rng.Rows(R).EntireRow.Delete
       
               N = N + 1
       
           End If
       
       Else
       
           If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
       
               Rng.Rows(R).EntireRow.Delete
       
               N = N + 1
       
           End If
       
       End If
   
   Next R
   
   EndMacro:
   
   Application.StatusBar = False
   Application.ScreenUpdating = True
   Application.Calculation = xlCalculationAutomatic
   MsgBox "Duplicate Rows Deleted: " & CStr(N)
   
   End Sub
   
   

5. It should now look something like this:
   
   (If there was a problem performing the copy/paste from this site, make sure the single quotation marks in front of the green text are indeed basic normal single quotation marks; you can do this by deleting and retyping them in the VBA Editor window here. Or, just download the sample file attached to this tutorial and copy the macro from there.)
6. Hit Alt + F11 to go back to the Excel window and select a cell within the column of duplicate values that you would like to remove:
   
7. Hit Alt + F8 to see the macro.
   
8. Select the one called DeleteDuplicateRows and then hit the Run button.  Once you do that a small window will appear telling you how many duplicates were removed:
   
9. Hit OK and you’re done!
   

This method works pretty much the same, though is not as robust, as the Remove Duplicates feature for Excel 2007 and later.

You cannot «undo» this action by hitting Ctrl + Z or anything else, so, be aware that whatever the macro removes is irreversible.  Have a backup of the original data made before running any macro in case an error happens.

If you want to keep this macro in your workbook, make sure to save the workbook in a format compatible with macros; when you go to save the file, Excel will tell you if there is an issue, and, if you don’t see a warning, it should save just fine in the spreadsheet.

Follow this tutorial to run the macro when you click a button in the worksheet.

Notes

If you are in Excel 2007 and later, use the built-in method to remove duplicates since it is so much easier compared to having to install a macro and since you can undo the action if you make a mistake.

If you use the macro, you cannot undo any changes it makes to the worksheet; always make a backup of your data before running a macro.

Be careful removing duplicates on spreadsheets with a lot of data spread-out throughout the worksheet because it might remove data to the right or left of your current data set.  The best thing to do is to have a separate worksheet that only contains the data that you want to check for duplicates.  This way, you are certain to not mess with any other data.

Make sure to download the accompanying spreadsheet for this tutorial.  It contains the macro listed here and some sample data for you to try everything out.

Similar Content on TeachExcel

Prevent Duplicate Values in Excel

Tutorial:
I show you how to prevent duplicate values being entered into Excel using Data Validation…

Delete Duplicate Rows

Macro: This macro will delete rows that appear twice in a list or worksheet. If two cells are…

Subscribe for Weekly Tutorials

BONUS: subscribe now to download our Top Tutorials Ebook!

В этом руководстве будет показано, как удалить дубликаты с помощью метода RemoveDuplicates в VBA.

Метод RemoveDuplicates

Когда данные импортируются или вставляются на лист Excel, они часто могут содержать повторяющиеся значения. Возможно, вам потребуется очистить входящие данные и удалить дубликаты.

К счастью, в объекте Range в VBA есть простой метод, который позволяет это сделать.

Синтаксис:

RemoveDuplicates ([Столбцы], [Заголовок]

• [Столбцы] — Укажите, в каких столбцах проверяются повторяющиеся значения. Все столбцы совпадают, чтобы считаться дубликатами.
• [Заголовок] — Есть ли у данных заголовок? xlNo (по умолчанию), xlYes, xlYesNoGuess

Технически оба параметра необязательны. Однако, если вы не укажете аргумент Columns, никакие дубликаты не будут удалены.

Значение по умолчанию для заголовка — xlNo. Конечно, лучше указать этот аргумент, но если у вас есть строка заголовка, маловероятно, что строка заголовка будет соответствовать как дубликат.

Замечания по использованию RemoveDuplicates

• Перед использованием метода RemoveDuplicates необходимо указать используемый диапазон.
• Метод RemoveDuplicates удалит все строки с найденными дубликатами, но сохранит исходную строку со всеми значениями.
• Метод RemoveDuplicates работает только со столбцами, а не со строками, но для исправления этой ситуации можно написать код VBA (см. Ниже).

Образцы данных для примеров VBA

Чтобы показать, как работает пример кода, используются следующие образцы данных:

Удалить повторяющиеся строки

Этот код удалит все повторяющиеся строки только на основе значений в столбце A:

Обратите внимание, что мы явно определили диапазон «A1: C8». Вместо этого вы можете использовать UsedRange. UsedRange определит последнюю использованную строку и столбец ваших данных и применит RemoveDuplicates ко всему этому диапазону:

UsedRange невероятно полезен, избавляя вас от необходимости явно определять диапазон.

После запуска этого кода ваш рабочий лист теперь будет выглядеть так:

Обратите внимание, что, поскольку был указан только столбец A (столбец 1), дубликат «Яблоки», ранее находившийся в строке 5, был удален. Однако количество (столбец 2) отличается.

Чтобы удалить дубликаты, сравнивая несколько столбцов, мы можем указать эти столбцы с помощью метода Array.

Удалить дубликаты, сравнивая несколько столбцов

Массив сообщает VBA о необходимости сравнения данных с использованием столбцов 1 и 2 (A и B).

Столбцы в массиве не обязательно должны располагаться в последовательном порядке.

В этом примере столбцы 1 и 3 используются для повторяющегося сравнения.

В этом примере кода для проверки дубликатов используются все три столбца:

Удаление повторяющихся строк из таблицы

Удалить дубликаты можно точно так же применить к таблице Excel. Однако синтаксис немного отличается.

Это приведет к удалению дубликатов в таблице на основе столбцов 1 и 3 (A и C). Однако он не приводит в порядок цветовое форматирование таблицы, и вы увидите цветные пустые строки, оставленные в нижней части таблицы.

Удаление дубликатов из массивов

Если вам нужно удалить повторяющиеся значения из массива, конечно, вы можете вывести свой массив в Excel, использовать метод RemoveDuplicates и повторно импортировать массив.

Однако мы также написали процедуру VBA для удаления дубликатов из массива.

Удаление дубликатов из строк данных с помощью VBA

Метод RemoveDuplicates работает только со столбцами данных, но, если подумать «нестандартно», вы можете создать процедуру VBA для работы со строками данных.

Предположим, что ваши данные на листе выглядят так:

У вас есть те же дубликаты, что и раньше, в столбцах B и E, но вы не можете удалить их с помощью метода RemoveDuplicates.

Ответ состоит в том, чтобы использовать VBA для создания дополнительного рабочего листа, копирования данных в него, транспонируя их в столбцы, удаляя дубликаты, а затем копируя их обратно, перемещая обратно в строки.

В этом коде предполагается, что исходные данные в строках хранятся на листе под названием «DataInRows».

После запуска кода ваш рабочий лист будет выглядеть так:

Дубликат «Яблоки» в столбце E был удален. Пользователь вернулся в чистое положение, без каких-либо посторонних рабочих листов, и весь процесс прошел гладко, без мерцания экрана или предупреждающих сообщений.