Tuesday, May 6, 2008

Delete/ Remove duplicate rows in Excel

Public Sub DeleteDuplicateRows()
'
' This macro deletes duplicate rows in the selection. Duplicates are
' counted in the COLUMN of the active cell.

Dim Col As Integer
Dim r As Long
Dim C As Range
Dim N As Long
Dim V As Variant
Dim Rng As Range

On Error GoTo EndMacro
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Col = ActiveCell.Column

If Selection.Rows.Count > 1 Then
Set Rng = Selection
Else
Set Rng = ActiveSheet.UsedRange.Rows
End If

N = 0
For r = Rng.Rows.Count To 1 Step -1
V = Rng.Cells(r, 1).Value
If Application.WorksheetFunction.CountIf(Rng.Columns(1), V) > 1 Then
Rng.Rows(r).EntireRow.Delete
N = N + 1
End If
Next r

EndMacro:

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

4 comments:

  1. hai

    can u please explain the code you mentioned here....!?

    thanks
    regards
    guru

    ReplyDelete
  2. hai

    can u please explain the code you mentioned here....!?

    thanks
    regards
    guru

    ReplyDelete
  3. Help me to add time to a date and time
    I will explain my problem with an example.

    30/01/2009 13:30 is the time.I want to add 36 hrs to it.

    But I should add it only during the working hours.(8:30 hours per hour a day(exclude week ends and holidays)).

    Assume working time to be from 08:00 to 16:30

    So the time after 36hrs added would be 05/02/2009 16:00.


    I can add only the date through workday function,which also excludes holidays and weekends.But I am not able to add the exact time.

    Can YOU help me to get it done through macros??

    Thanks in Advance

    ReplyDelete
  4. I want to delete the rows where there is no value in the first column (for the current region).
    How can I ammend your code to acomplish this?

    ReplyDelete