Deleting duplicates i.e. "Anne Bell"

[Ctech]

Hi

Hi have a list of names, however some is duplicated. Is there a easy way to delete all the duplicates.. some are also duplicated more than once...

Thanks

[Bob Phillips]

In an adjacent column, add this formula

=COUNTIF($A$1:A2,A2)>1

and copy down

Then filter that column for the TRUE value, and delete visible rows.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ctech" <[email protected]> wrote in
message news:[email protected]...
>
> Hi
>
> Hi have a list of names, however some is duplicated. Is there a easy
> way to delete all the duplicates.. some are also duplicated more than
> once...
>
> Thanks
>
>
> --
> Ctech
> ------------------------------------------------------------------------
> Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
> View this thread: http://www.excelforum.com/showthread...hreadid=486784
>

[Ctech]

Thanks, that worked fine.

However now I want to implment this forumla into a more advanced macro.


I have made a User Form, with a RefEdit for the Range, and two radio buttons.

How can I implement this fomula to work with a different range each time.
I have made some code, however I'm not even close to make it.. All help needed. Thanks



Macro:

Private Sub cmd_OK_Click()

Dim mRange
Dim mRows As Long

mRange = Duplicates.RefEdit1
mRows = Range(mRange).Rows.Count


If cb_delete Then

' My code

End If




If cb_color Then
****Not needed yet ****
End If

Unload Duplicates

End Sub




Private Sub cmd_Cancel_Click()

Unload Duplicates

End Sub

[Bob Phillips]

How about this

Private Sub cmd_OK_Click()
Dim mRange As Range
Dim mRows As Long

Set mRange = Range(Duplicates.RefEdit1)
mRows = mRange.Rows.Count

If cb_Delete Then
Columns("B:B").Insert
Range("B1").Formula = "=COUNTIF($A$1:A1,A1)"
Range("B1").AutoFill Range("B1").Resize(mRows)
Rows(1).Insert
Range("B1").Value = "Temp"
mRange.Offset(-1, 1).Resize(11).AutoFilter Field:=1,
Criteria1:=">1", Operator:=xlAnd
mRange.Offset(0,
1).Resize(11).SpecialCells(xlCellTypeVisible).EntireRow.Delete
Rows(1).Delete
Columns("B:B").Delete
End If

If cb_Color Then
'****Not needed yet ****
End If

Unload Duplicates

End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ctech" <[email protected]> wrote in
message news:[email protected]...
>
> Thanks, that worked fine.
>
> However now I want to implment this forumla into a more advanced
> macro.
>
>
> I have made a User Form, with a RefEdit for the Range, and two radio
> buttons.
>
> How can I implement this fomula to work with a different range each
> time.
> I have made some code, however I'm not even close to make it.. All help
> needed. Thanks
>
>
>
> Macro:
>
> Private Sub cmd_OK_Click()
>
> Dim mRange
> Dim mRows As Long
>
> mRange = Duplicates.RefEdit1
> mRows = Range(mRange).Rows.Count
>
>
> If cb_delete Then
>
> ' My code
>
> End If
>
>
>
>
> If cb_color Then
> ****Not needed yet ****
> End If
>
> Unload Duplicates
>
> End Sub
>
>
>
>
> Private Sub cmd_Cancel_Click()
>
> Unload Duplicates
>
> End Sub
>
>
> --
> Ctech
> ------------------------------------------------------------------------
> Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
> View this thread: http://www.excelforum.com/showthread...hreadid=486784
>

[Ctech]

Your macro worked fine, when it was used on a range in Column A, however I need it to work everywhere in the wb.

So I've made my own little marco, which should fix this problem. However Im having some problems with the addtounion part, (Im getting Run error "5")
And also how to change the formula to change depending on the range selected in the UserForm.

See error in the macro is with red font.

Macro:

Dim DelRg As Range

Private Sub cmd_OK_Click()
Dim mRange As Range
Dim mTest As Range
Dim mRows As Long
Dim mCell As Range
Dim Cell As Range
Set DelRg = Nothing

Set mRange = Range(Duplicates.RefEdit1)
Set mTest = mRange.Offset(0, 1)
Set mCell = mTest.Cells(1)
mRows = mRange.Rows.Count

If cb_delete Then
mTest.EntireColumn.Insert Shift:=x1ToLeft
Set mTest = mRange.Offset(0, 1)
mTest.Select


mTest.Formula = "=COUNTIF($A$1:A1,A1)"

For Each Cell In mTest
If Cell.Value > 1 Then
AddToUnion Cell

End If
Next Cell


If Not DelRg Is Nothing Then DelRg.EntireRow.Delete Shift:=x1up

mTest.EntireColumn.Delete Shift:=x1ToLeft

End If

If cb_color Then
'****Not needed yet ****
End If

Unload Duplicates

End Sub
Sub AddToUnion(Cell As Range)

Set DelRg = Union(DelRg, Cell)

End Sub

[Bob Phillips]

Why not filter as I did?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ctech" <[email protected]> wrote in
message news:[email protected]...
>
> Your macro worked fine, when it was used on a range in Column A, however
> I need it to work everywhere in the wb.
>
> So I've made my own littile marco, which should fix this problem.
> However Im having some problems with the addtounion part.
>
> Macro:
>
> Dim DelRg As Range
>
> Private Sub cmd_OK_Click()
> Dim mRange As Range
> Dim mTest As Range
> Dim mRows As Long
> Dim mCell As Range
> Dim Cell As Range
> Set DelRg = Nothing
>
> Set mRange = Range(Duplicates.RefEdit1)
> Set mTest = mRange.Offset(0, 1)
> Set mCell = mTest.Cells(1)
> mRows = mRange.Rows.Count
>
> If cb_delete Then
> mTest.EntireColumn.Insert Shift:=x1ToLeft
> Set mTest = mRange.Offset(0, 1)
> mTest.Select
>
>
> mTest.Formula = "=COUNTIF($A$1:A1,A1)"
>
> For Each Cell In mTest
> If Cell.Value > 1 Then
> AddToUnion Cell
>
> End If
> Next Cell
>
>
> If Not DelRg Is Nothing Then DelRg.EntireRow.Delete Shift:=x1up
>
> mTest.EntireColumn.Delete Shift:=x1ToLeft
>
> End If
>
> If cb_color Then
> '****Not needed yet ****
> End If
>
> Unload Duplicates
>
> End Sub
> Sub AddToUnion(Cell As Range)
>
> Set DelRg = Union(DelRg, Cell)
>
> End Sub
>
>
> --
> Ctech
> ------------------------------------------------------------------------
> Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
> View this thread: http://www.excelforum.com/showthread...hreadid=486784
>

[Ctech]

I tried, but I couldn't manage to get it to work.
I'll happly use your method, but either way I need some more help please.


Thanks all

[Bob Phillips]

What problem are you having?

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ctech" <[email protected]> wrote in
message news:[email protected]...
>
> Your macro worked fine, when it was used on a range in Column A, however
> I need it to work everywhere in the wb.
>
> So I've made my own littile marco, which should fix this problem.
> However Im having some problems with the addtounion part.
>
> Macro:
>
> Dim DelRg As Range
>
> Private Sub cmd_OK_Click()
> Dim mRange As Range
> Dim mTest As Range
> Dim mRows As Long
> Dim mCell As Range
> Dim Cell As Range
> Set DelRg = Nothing
>
> Set mRange = Range(Duplicates.RefEdit1)
> Set mTest = mRange.Offset(0, 1)
> Set mCell = mTest.Cells(1)
> mRows = mRange.Rows.Count
>
> If cb_delete Then
> mTest.EntireColumn.Insert Shift:=x1ToLeft
> Set mTest = mRange.Offset(0, 1)
> mTest.Select
>
>
> mTest.Formula = "=COUNTIF($A$1:A1,A1)"
>
> For Each Cell In mTest
> If Cell.Value > 1 Then
> AddToUnion Cell
>
> End If
> Next Cell
>
>
> If Not DelRg Is Nothing Then DelRg.EntireRow.Delete Shift:=x1up
>
> mTest.EntireColumn.Delete Shift:=x1ToLeft
>
> End If
>
> If cb_color Then
> '****Not needed yet ****
> End If
>
> Unload Duplicates
>
> End Sub
> Sub AddToUnion(Cell As Range)
>
> Set DelRg = Union(DelRg, Cell)
>
> End Sub
>
>
> --
> Ctech
> ------------------------------------------------------------------------
> Ctech's Profile:
http://www.excelforum.com/member.php...o&userid=27745
> View this thread: http://www.excelforum.com/showthread...hreadid=486784
>