Certain spreadsheets are used and updated a lot. These often are filled with data that is repeated multiple times. There are ways to find and delete any rows that contain that duplicate data, but one of the most simple and automatic ways to accomplish this is with a Visual Basic Macro. Excel contains an application for programming called Excel VBA, which can be used to make any number of Macros---pieces of code designed to do a custom job whenever summoned.
- Skill level:
Other People Are Reading
Things you need
- Microsoft Excel
Open your spreadsheet in Excel and open your VBA editor by pressing Alt and the F11 key on your computer.
Start a new project by right-clicking your workbook on the right-hand menu and choosing the "New" option.
Start the new function with "Sub DeleteDuplicateRows()." This informs the VBA compiler that there is a new subroutine that it should be aware of.
Create the variables with the following code:
Dim Row As Long Dim Count As Long Dim Compare As Variant Dim Range As Range
This tells the system to set aside the four variables (Row, Count, Comparison and Range) to use in the code. You can use whatever variable names make more sense to you, but remember to change them in the later code.
Set up the macro with some more basic code:
On Error GoTo EndMacro Application.ScreenUpdating = False Application.Calculation = xlCalculationManual
Set Range = Application.Intersect(ActiveSheet.UsedRange, _ ActiveSheet.Columns(ActiveCell.Column)) Application.StatusBar = "Processing Row: " & Format(Rng.Row, "#,##0")
The first group of code tells the system to run the macro if it runs into any problems. The second group sets the range variable according to whatever the user has selected.
Type in the following code:
Count = 0 For Row = Range.Rows.Count To 2 Step -1 If Row Mod 500 = 0 Then Application.StatusBar = "Processing Row: " & Format(R, "#,##0") End If
Compare = Range.Cells(Row, 1).Value
This tells the system to step through each of the rows selected by the user, and change the Compare variable to that row each time it moves on.
Input the following code next:
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
These statements are the ones that actually tell the system to compare the Compare variable to the next cell and delete the row if the two are the same.
Press enter to insert a couple of new lines, and then type in "Next Row". This tells the system to stop looking at the first row, and move onto the next.
Close out the function with:
Application.StatusBar = False Application.ScreenUpdating = True Application.Calculation = xlCalculationAutomatic MsgBox "Duplicate Rows Deleted: " & CStr(N)
These final lines tell the system how to close the subroutine out, and also to display a helpful message box informing the user that the Macro actually did what it was supposed to do.
Save the spreadsheet. Save a second, backup copy before using the Macro. Either copy and paste the file into a backup folder, or us the "Save as" file to save the second copy---you will need to close Excel and reopen it.
Click and drag your cursor around the most important column of data. This is the one for which all entries should differ---as in, if everything had been entered correctly, there wouldn't be any duplicate entries.
Open the "Tools" menu and select "Macros." This opens the macros dialogue, allowing you to choose and run your macro.
Select your macro; if you used the example above, it will be called "DelDuplicateR." Then press the "Run" button. The Macro will run automatically, and pop up a message indicating that the duplicates have been deleted.
Tips and warnings
- This only works if the content of the two cells are exactly the same. It will not work if they differ by even a small amount.
- Macros cannot be undone using the "Undo" command under the "Edit" menu or Ctrl and Z. Before using any macros, you should back up your data to prevent losing it entirely.
- 20 of the funniest online reviews ever
- 14 Biggest lies people tell in online dating sites
- Hilarious things Google thinks you're trying to search for