macro find and delete duplicates in a spread sheet.

[Marc]

Hi,

Does anyone know, or point me in the right direction, how to have Excel
find duplicate cells and delete them. For example, I have 2 columns of
numbers. I need Excel to read the first cell in column A and look for
a match in column B, and if it finds it, delete both. Then to repeat
for the whole column. Any help would be appreciated.

Thanks,
Marc

 

[Tod]

Try it.

Sub DeleteMatches()
Dim k As Integer
Dim i As Integer
Dim j As Integer
Dim MatchArray()

k = 0
For i = ActiveSheet.Range("B65536").End(xlUp).Row To 2
Step -1
For Each Cell In ActiveSheet.Range("A2:A" &
ActiveSheet.Range("A65536").End(xlUp).Row)
If Cell.Value = ActiveSheet.Cells(i, 2).Value
Then
ActiveSheet.Cells(i, 2).Delete shift:=xlUp
ReDim Preserve MatchArray(j)
MatchArray(j) = Cell.Value
End If
Next Cell
Next i
If Not IsArray(MatchArray) Then
For i = ActiveSheet.Range("A65536").End(xlUp).Row
To 2 Step -1
For j = 0 To UBound(MatchArray)
If ActiveSheet.Cells(i, 1).Value =
MatchArray(j) Then
ActiveSheet.Cells(i, 1).Delete
shift:=xlUp
End If
Next j
Next i
End If

End Sub

 

[Frank Kabel]

Hi
see
http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows

 

[marc schnyder]

That's perfect!! Thanks many, many thanks!


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 

[lannykint]

Duplicate Files Deleter (http://DuplicateFilesDeleter.com) is a simple, buteffective tool to locate duplicate files in one or more selected search paths. It scans the files and compares them based on Byte for Byte Comparison, which ensures 100% accuracy. You can then choose to delete the selected duplicate or original files. The program is multi-threaded and performs scans quickly.

 

[GS]

Duplicate Files Deleter (http://DuplicateFilesDeleter.com) is a

simple, but effective tool to locate duplicate files in one or more
selected search paths. It scans the files and compares them based on
Byte for Byte Comparison, which ensures 100% accuracy. You can then
choose to delete the selected duplicate or original files. The
program is multi-threaded and performs scans quickly.

Sounds dubious! Please explain how there can be duplicate files in one
path.

Also, please explain how this relates to Excel VBA.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion