Remove duplicate rows based on value of cell in column

Question

I have the following script that works well in google docs --> sheets. It doesn't work well with a lot of rows. I am guessing because of the array that keeps getting bigger that tracks the values.

I need a script I can run in MS EXCEL that will remove rows that have a duplicate value in a column. (Unless the column is "")

Google docs script that works for small files:

function removeDuplicates()
{
  var s = SpreadsheetApp.getActiveSheet();
  var c = Browser.inputBox("Please", "Type in the column name (e.g.: A, B, etc.)", Browser.Buttons.OK_CANCEL);
  var r, v;
  var aValues = [];
  try
  {
    if(c != "cancel")
    {
      r = 2; // first row is row two
      while (r <= s.getLastRow())
      {
        v = s.getRange(c + r).getValue();
        if(v != "")
        {
          if(aValues.indexOf(v) == -1)
          {
            aValues.push(v);
          }
          else
          {
            s.deleteRow(r);
            continue;
          }
        }
        r++;
      }
      Browser.msgBox("Duplicates removed!");
    }
  } catch (e) {Browser.msgBox("Error Alert:", e.message, Browser.Buttons.OK);}
}

Any help would be appreciated.

Have you tried the ribbon command's Data ► Data Tools ► Remove Duplicates or have you got an earlier version of Excel? Is this to be part of a larger VBA routine? — user4039065
– user4039065, Commented Nov 3, 2014 at 21:17
It seems remove duplicates removes duplicate blank rows too (which I need to keep). This is just a simple routine I need to be able to run on multiple sheets — Chris Muench
– Chris Muench, Commented Nov 3, 2014 at 21:22
You're right of course. I was confusing two worksheet operations that did not reply upon one another. (AutoFilter for non-blank then Remove Duplicates does not dedupe on visible cells only) — user4039065
– user4039065, Commented Nov 3, 2014 at 21:32

score 1 · Accepted Answer · 2014-11-04 18:12:36Z

1

Here is something that seems to fit the bill.

Sub dedupe_ignore_blanks()
    Dim r As Long, v As Long, vVALs As Variant, sCOL As String
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    With ActiveSheet.Cells(1, 1).CurrentRegion
        sCOL = "B"
        sCOL = Application.InputBox("Type in the column name (e.g.: A, B, etc.)", _
            "Please", sCOL, 250, 75, "", , 2)
        If CBool(Len(sCOL)) And sCOL <> "False" Then
            For r = .Rows.Count To 2 Step -1
                If Application.CountIf(.Columns(sCOL), .Cells(r, sCOL).Value) > 1 Then _
                    .Rows(r).EntireRow.Delete
            Next r
        End If
    End With
FallThrough:
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

I gathered from your code snippet that you had a header row in the data row 1. The Application.CountIF does not count blank cells.

edited Nov 4, 2014 at 18:12

answered Nov 3, 2014 at 22:02

user4039065

Sign up to request clarification or add additional context in comments.

3 Comments

Chris Muench Over a year ago

Magically! Took awhile to run but it got the job done

user4039065 Over a year ago

@ChrisMuench - thanks for the follow up. If this is a repetitious task and you wish to make iot more efficient, turning screenupdating and enableevents off and setting calculation to manual at the beginning of the operation and restoring them at the end will significantly improve the runtime. Improved above.

Chris Muench Over a year ago

It is not something I do often. It probably took 20-30 minutes on a 100000 row spreadsheet which is very acceptable

Collectives™ on Stack Overflow

Remove duplicate rows based on value of cell in column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related