Hi
a while ago a nice chap called John sent me a macro which deletes rows where
the data is identical. This has been extremely helpful for some time. The
data I deal with is names and addressess in columns A-J.
I need to adapt or use a macro that can delete rows where the data in column
E (first line of address) is identical but where the data in other columns
may be different due to typing errors.
I would like to understand how to do this so that I can then maybe change
the code to say delete rows where it is maybe another column where the data
is identical.
This is the code/instruction I have in my worksheet at the moment:

Sub DeleteDups()

Dim ws1 As Worksheet
Dim lastrow As Long
Dim r As Long


Set ws1 = Worksheets("ORIGINAL")
With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.And(.Cells(r, "A") = .Cells(r - 1, "A"), .Cells(r,
"C") = _
.Cells(r - 1, "C"), .Cells(r, "J") = .Cells(r - 1, "J")) Then
.Rows(r).Delete shift:=xlUp
End If

Next r

End With



End Sub

Many Thanks
Ani

Rather than solve your problem, I will explain how this macro works, so
you can hopefully change it yourself


Set ws1 = Worksheets("ORIGINAL")
This line says that the macro will work on the spreadsheet titled
"ORIGINAL" If you want to run it on another spreadsheet, you will need
to change this line. I suggest you replace this line with
Set ws1 = ActiveSheet

With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row

The above code finds the last row of your data table and selects it.

For r = lastrow To 2 Step -1

The above starts a process where you start at the last row, and then
move up one row at a time until you get to the second to last row (I
assume the last(top) row contains headings).

If Application.And(.Cells(r, "A") = .Cells(r - 1, "A"), .Cells(r,
"C") = .Cells(r - 1, "C"), .Cells(r, "J") = .Cells(r - 1, "J")) Then
..Rows(r).Delete shift:=xlUp

The above code is where all the action happens. Application.And means
all three of the following tests must be true before a row is deleted.
..Cells(r, "A") = .Cells(r - 1, "A")
..Cells(r,"C") = .Cells(r - 1, "C")
..Cells(r, "J") = .Cells(r - 1, "J")

ie .Cells(r, "A") = .Cells(r - 1, "A") says that the current row (data
in column A) must match the row above (data in column A)
etc
etc

So basically if everying in column A, C and J are the exactly the same,
then the bottom row of the 2 rows will be deleted.

If you want to change this so that it checks for the address in column
E, you could either add a new condtion to the end of the
Application.And string like this

If Application.And(.Cells(r, "A") = .Cells(r - 1, "A"), .Cells(r,
"C") = .Cells(r - 1, "C"), .Cells(r, "J") = .Cells(r - 1, "J"),
Cells(r, "E") = .Cells(r - 1, "E"))


Doing this will mean that everything in column A, C, J and E must match
for the deletion to occur. Alternatively if this is not what you want,
just change the formula to suit.

Hope this helps

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=552985

I cant thank you enough! That is brilliant, I now have a much better
understanding of how these work, I may not be able to write a macro from
scratch but I will have a go at maybe looking for ones that I can adapt
instead of asking the same questions as everyone else every time I get stuck!
Many Thanks
Ani


"Mallycat" wrote:


Rather than solve your problem, I will explain how this macro works, so
you can hopefully change it yourself


Set ws1 = Worksheets("ORIGINAL")
This line says that the macro will work on the spreadsheet titled
"ORIGINAL" If you want to run it on another spreadsheet, you will need
to change this line. I suggest you replace this line with
Set ws1 = ActiveSheet

With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row

The above code finds the last row of your data table and selects it.

For r = lastrow To 2 Step -1

The above starts a process where you start at the last row, and then
move up one row at a time until you get to the second to last row (I
assume the last(top) row contains headings).

If Application.And(.Cells(r, "A") = .Cells(r - 1, "A"), .Cells(r,
"C") = .Cells(r - 1, "C"), .Cells(r, "J") = .Cells(r - 1, "J")) Then
.Rows(r).Delete shift:=xlUp

The above code is where all the action happens. Application.And means
all three of the following tests must be true before a row is deleted.
.Cells(r, "A") = .Cells(r - 1, "A")
.Cells(r,"C") = .Cells(r - 1, "C")
.Cells(r, "J") = .Cells(r - 1, "J")

ie .Cells(r, "A") = .Cells(r - 1, "A") says that the current row (data
in column A) must match the row above (data in column A)
etc
etc

So basically if everying in column A, C and J are the exactly the same,
then the bottom row of the 2 rows will be deleted.

If you want to change this so that it checks for the address in column
E, you could either add a new condtion to the end of the
Application.And string like this

If Application.And(.Cells(r, "A") = .Cells(r - 1, "A"), .Cells(r,
"C") = .Cells(r - 1, "C"), .Cells(r, "J") = .Cells(r - 1, "J"),
Cells(r, "E") = .Cells(r - 1, "E"))


Doing this will mean that everything in column A, C, J and E must match
for the deletion to occur. Alternatively if this is not what you want,
just change the formula to suit.

Hope this helps

Matt


--
Mallycat
------------------------------------------------------------------------
Mallycat's Profile: http://www.excelforum.com/member.php...o&userid=35514
View this thread: http://www.excelforum.com/showthread...hreadid=552985

You might also want to check out Mr. Excels "Fuzzy Match" functions:

http://www.mrexcel.com/board2/viewto...=974873#974873

The mechanics are quite complicated, but the controls are easy to use.

Mike F

"ani_unicorn" wrote in message
...
Hi
a while ago a nice chap called John sent me a macro which deletes rows
where
the data is identical. This has been extremely helpful for some time.
The
data I deal with is names and addressess in columns A-J.
I need to adapt or use a macro that can delete rows where the data in
column
E (first line of address) is identical but where the data in other columns
may be different due to typing errors.
I would like to understand how to do this so that I can then maybe change
the code to say delete rows where it is maybe another column where the
data
is identical.
This is the code/instruction I have in my worksheet at the moment:

Sub DeleteDups()

Dim ws1 As Worksheet
Dim lastrow As Long
Dim r As Long


Set ws1 = Worksheets("ORIGINAL")
With ws1
lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
For r = lastrow To 2 Step -1
If Application.And(.Cells(r, "A") = .Cells(r - 1, "A"), .Cells(r,
"C") = _
.Cells(r - 1, "C"), .Cells(r, "J") = .Cells(r - 1, "J")) Then
.Rows(r).Delete shift:=xlUp
End If

Next r

End With



End Sub

Many Thanks
Ani