MacOS – How to delete duplicate rows in Microsoft Excel 2011

macosms office

Every 2nd Thursday our team has to produce reports from data provided to us in a Excel spreadsheet. Typically this data has about 23,000 rows of data. The problem is that since June these spreadsheets now have duplicated rows, due to a problem in the data warehouse they’re produced in.

I don’t understand anything about that side of thing, but what I do know is that we need to find an easy way to delete every second row. At present we are splitting the data into 3 spreadsheets and I have 3 staff members deleting every 2nd row in their respective spreadsheet. When they’re all finished we merge them back into one spreadsheet to produce our reports. This is a big time waster (it still takes hours to do), creates a lot of stress due to other work getting delayed, and ruins morale in the team.

We’re using Excel 2011 on iMacs running El Capitan, so I was thinking that a macro could do this, right? Can someone confirm if this is the case and if yes how we would go about it?

Best Answer

This answer assumes:

  • you have a header row and, because of that, the first row containing your data is Row 2.

Solution

  1. Open the Excel spreadsheet in its original state (i.e. All duplicate rows are adjacent to each other)
  2. Go to Tools > Macro > Visual Basic Editor
  3. This will bring up a Project window with the name of your first sheet highlighted - Double-click on the sheet name

  4. In the code window that appears, enter the code below (you should be able to just copy & paste it)

    Sub DeleteEverySecondRow()
    For i = Cells(Rows.Count, "a").End(xlUp).Row To 2 Step -2
        Rows(i).Delete
    Next i
End Sub

  5. Close the window and return back to your spreadsheet

  6. Now go to Tools > Macro > Macros…
  7. In the Macro window that appears, select the DeleteEverySecondRow macro and click on the Run button
  8. Now wait a while for the macro to finish (how long it takes depends on how many rows you have - but it should take no more than a few minutes at most)
  9. After it’s finished every second row will be deleted

Any problems, please let me know.

NOTE

I've answered this question on the basis of the original title and the requirement you've specified (i.e. to delete every second row). However, keep in mind that if you've sorted the data in such a way that you're duplicate rows are no longer adjacent to each other, then this macro will not achieve what you actually need.

Related Question