Deleting duplicates i.e. "Anne Bell"
 

Hi

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

Thank

--
Ctec
-----------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=48678

Deleting duplicates i.e. "Anne Bell"
 

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" wrote in
message ...

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

Deleting duplicates i.e. "Anne Bell"
 

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

Deleting duplicates i.e. "Anne Bell"
 

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).Enti reRow.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" wrote in
message ...

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

Deleting duplicates i.e. "Anne Bell"
 

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

Deleting duplicates i.e. "Anne Bell"
 

Why not filter as I did?

--

HTH

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


"Ctech" wrote in
message ...

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

Deleting duplicates i.e. "Anne Bell"
 

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 hel
please.


Thanks al

--
Ctec
-----------------------------------------------------------------------
Ctech's Profile: http://www.excelforum.com/member.php...fo&userid=2774
View this thread: http://www.excelforum.com/showthread.php?threadid=48678

Deleting duplicates i.e. "Anne Bell"
 

What problem are you having?

--

HTH

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


"Ctech" wrote in
message ...

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