Hi,

In one sheet I have many duplicate rows and I wanted to remove
completely and left non duplicate rows.Pls help how to do in VBA.
Example:
Before
A B
Mary 50
Jane 20
Sam 10
Jery 30
Jane 20
Sam 10

After
A B
Mary 50
Jery 30
Left only non duplicate rows.
Pls help.Thanks

is col A unique or is it A B

ie will you ever have
Mary, 10
Mary, 20

if not then

Option Explicit

Sub sortanddelete()
Dim rw As Long

'sort the data
With Range("A1:A" & Range("A:A").Rows.Count)
.Sort Range("A1")
End With
'strip out duplicates
For rw = Range("A1").End(xlDown).Row To 2 Step -1
If Cells(rw, 1) = Cells(rw - 1, 1) Then Rows(rw).Delete
Next

End Sub




"geniusideas" wrote:

> Hi,
>
> In one sheet I have many duplicate rows and I wanted to remove
> completely and left non duplicate rows.Pls help how to do in VBA.
> Example:
> Before
> A B
> Mary 50
> Jane 20
> Sam 10
> Jery 30
> Jane 20
> Sam 10
>
> After
> A B
> Mary 50
> Jery 30
> Left only non duplicate rows.
> Pls help.Thanks
>
>

On Aug 27, 10:50*pm, Patrick Molloy
<PatrickMol...@discussions.microsoft.com> wrote:
> is col A unique or is it A B
>
> ie will you ever have
> Mary, 10
> Mary, 20
>
> if not then
>
> Option Explicit
>
> Sub sortanddelete()
> * * Dim rw As Long
>
> 'sort the data
> * * With Range("A1:A" & Range("A:A").Rows.Count)
> * * * * .Sort Range("A1")
> * * End With
> 'strip out duplicates * *
> * * For rw = Range("A1").End(xlDown).Row To 2 Step -1
> * * * * If Cells(rw, 1) = Cells(rw - 1, 1) Then Rows(rw).Delete
> * * Next
>
> End Sub
>
> "geniusideas" wrote:
> > Hi,
>
> > In one sheet I have many duplicate rows and I wanted to remove
> > completely and left non duplicate rows.Pls help how to do in VBA.
> > Example:
> > Before
> > * *A * * * * * * * * * B
> > Mary * * * * * * * *50
> > Jane * * * * * * * *20
> > Sam * * * * * * * *10
> > Jery * * * * * * * * 30
> > Jane * * * * * * * *20
> > Sam * * * * * * * *10
>
> > After
> > * A * * * * * * * * * B
> > Mary * * * * * * * *50
> > Jery * * * * * * * * 30
> > Left only non duplicate rows.
> > Pls help.Thanks

Dear Patrick,

Both col is unique, mean I should have
Mary, 10
Mary, 20

Thanks