delete dulipate rows by checking each and every cell in a row till end.

[sreedhar]

hi friends,

iam trying to write macro for delete rows in a set of data by checking
which row is greater than the other row and delete the lesser row.

for example

A B C D E F

1 1 0 1 1 0 1
2 1 0 0 1 0 1
3 0 1 0 0 1 0
4 1 0 1 0 1 1
5 0 0 1 0 0 1

FOR this data the marco should delete rows which are >= to other rows

answer should be like this


A B C D E F

1 1 0 1 1 0 1
2
3 0 1 0 0 1 0
4 1 0 1 0 1 1
5


2ND row is delete because 1st row is greater than the 2nd row
5th row is deleted because 4th row is greater than the 5th row


can any one plz help me rearding this marco.
thanks

sreedhar

 

[Tom Ogilvy]

Sub ab()
Dim lastrow As Long, i As Long
Dim cell As Range, c As Range
Dim sStr1 As String, sStr2 As String
lastrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lastrow
Set cell = Cells(i, 1).Resize(1, 6)
sStr1 = "": sStr2 = ""
For Each c In cell
sStr2 = sStr2 & c.Value
sStr1 = sStr1 & c.Offset(-1, 0).Value
Next
If sStr2 < sStr1 Then
cell.ClearContents
End If
Next
End Sub

 

[sreedhar]

thanks for u'r help TOM,

actually u'r marco is comparing 1st row with all remaing rows and
clearing the rows which are lessthan it.

but what i need extra in this code is after checking with the 1st
row it should check the remaining rows with next row(2nd row) and then
it should go for next row (3rd row if exist) else next row. like this
way......... till thelast row checks with all rows.


once again thanks for u'r help

sree

 

[Tom Ogilvy]

No, that actually isn't what my macro is doing.

It reproduces your results. If it did what you said, it would delete all
rows after the first in your example.

If that isn't what you want, your meandering additional description adds
little to my comprehension. Perhaps someone else can understand you.

 

[sreedhar]

hi tom,


just i want to add some more to code to get the need result.


After clearing the entrie row , the row should be resize from the dow
row

for ex:

1 0 0 1 0
0 0 0 1 0
1 0 1 0 1
1 1 0 1 1
.................
. .......

after runing coding

1 0 0 1 0

1 0 1 0 1
1 1 0 1 1
...............
..............
after getting this output .
what i need is a marco for resize it to empty row from down
like this

1 0 0 1 0
1 0 1 0 1
1 1 0 1 1
.............
...............
can u plz, help regarding this......
thanks
sre