hi,

i want to compare rows and delete the rows

previously i got an marco help from the group but it is comparing the
whole string
of the row

for example

it is comparing rows

1 0 1 0
1 0 1 1
0 1 0 0

for these rows the marco is deleting the thrid row because 1010 is
0100
but it shouldn't delete it because we have to compare by cell by cell
in the whole row

1 0 ,0 <1 ,1 0,0 = 0

but 0<1
so it should not delete the row.

the marco is as follows

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, 100)
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

but i need a marco which compares cell by cell in the whole row and
delete if all cells are than all cells in the other row else compare
with other row till end of xlsheet.

So, can any one plz help me A.S.P

thanks
sree


--
sreedhar
------------------------------------------------------------------------
sreedhar's Profile: http://www.excelforum.com/member.php...o&userid=27582
View this thread: http://www.excelforum.com/showthread...hreadid=474533

You could concatenate the data from each column into a master column and
then test for duplicate columns.

http://www.cpearson.com/excel/duplicat.htm

"sreedhar" wrote in
message ...

hi,

i want to compare rows and delete the rows

previously i got an marco help from the group but it is comparing the
whole string
of the row

for example

it is comparing rows

1 0 1 0
1 0 1 1
0 1 0 0

for these rows the marco is deleting the thrid row because 1010 is
0100
but it shouldn't delete it because we have to compare by cell by cell
in the whole row

1 0 ,0 <1 ,1 0,0 = 0

but 0<1
so it should not delete the row.

the marco is as follows

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, 100)
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

but i need a marco which compares cell by cell in the whole row and
delete if all cells are than all cells in the other row else compare
with other row till end of xlsheet.

So, can any one plz help me A.S.P

thanks
sree


--
sreedhar
------------------------------------------------------------------------
sreedhar's Profile:
http://www.excelforum.com/member.php...o&userid=27582
View this thread: http://www.excelforum.com/showthread...hreadid=474533

sree,
?
Jim Cone
San Francisco, USA
'-------------------------
Sub ab()
Dim lastrow As Long, i As Long
Dim cell As Range, c As Range
'Dim sStr1 As String, sStr2 As String
Dim blnAlert As Boolean
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To lastrow
Set cell = Cells(i, 1).Resize(1, 100)
'sStr1 = "": sStr2 = ""
For Each c In cell
If c.Value c(0, 1).Value Then
blnAlert = True
Exit For
End If
'sStr2 = sStr2 & c.Value
'sStr1 = sStr1 & c.Offset(-1, 0).Value
Next
'If sStr2 < sStr1 Then
If Not blnAlert Then
cell.ClearContents
Else
blnAlert = False
End If
Next
Set c = Nothing
Set cell = Nothing
End Sub
'--------------------------

"sreedhar"
wrote in message

hi,
i want to compare rows and delete the rows
previously i got an marco help from the group but it is comparing the
whole string of the row
for example

it is comparing rows

1 0 1 0
1 0 1 1
0 1 0 0

for these rows the marco is deleting the thrid row because 1010 is
0100
but it shouldn't delete it because we have to compare by cell by cell
in the whole row

1 0 ,0 <1 ,1 0,0 = 0

but 0<1
so it should not delete the row.
the marco is as follows

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, 100)
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

but i need a marco which compares cell by cell in the whole row and
delete if all cells are than all cells in the other row else compare
with other row till end of xlsheet.
So, can any one plz help me A.S.P
thanks
sree

hi jim ,

thanks for u'r help

but it is n't working ,it is comparing the cells but when it is or =
also it is not deleteing can u plz check code once and make correctio
to it. where as it is comparing cells but deletion is not performing.

plz , help me regarding this,

thanks
sre

--
sreedha
-----------------------------------------------------------------------
sreedhar's Profile: http://www.excelforum.com/member.php...fo&userid=2758
View this thread: http://www.excelforum.com/showthread.php?threadid=47453

sree,

I am not sure what you want.
Did you try making changes to the code and trying it out?
Try changing "" to "=" or "<=" or "<" and see what it does.

Jim Cone


"sreedhar" wrote in message

hi jim ,
thanks for u'r help
but it is n't working ,it is comparing the cells but when it is or =
also it is not deleteing can u plz check code once and make correction
to it. where as it is comparing cells but deletion is not performing.
plz , help me regarding this,
thanks
sree

hi jim,

i have try changing the code to <=, = ,< but also it is not clearin
any rows even if it true.

i have try for rows

1 0 1 1
0 1 1 0
0 0 0 0
1 0 0 0

but the code is not clearing or deteleting any row.what i want is fo
the above rows it should delete 3rd and 4th row because they ar
lessthan and equal to every cell in 1st row.

2nd row can't be deleted because 2nd cell " 1 "geraterthan the 2nd cel
"0" of 1st row.

so ,plz help me

thanks
sre

--
sreedha
-----------------------------------------------------------------------
sreedhar's Profile: http://www.excelforum.com/member.php...fo&userid=2758
View this thread: http://www.excelforum.com/showthread.php?threadid=47453

With this data in the range A1:D4 then try:

Sub DelThem()
Dim i As Long
Dim j As Integer
Dim eRow As Long
Dim del As Boolean
eRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = eRow To 2 Step -1
For j = 1 To 4
If Cells(i, j).Value Cells(1, j).Value Then
del = True
Exit For
End If
Next j
If Not del Then Rows(i).EntireRow.Delete
Next i
End Sub

Hope this helps
Rowan

sreedhar wrote:
hi jim,

i have try changing the code to <=, = ,< but also it is not clearing
any rows even if it true.

i have try for rows

1 0 1 1
0 1 1 0
0 0 0 0
1 0 0 0

but the code is not clearing or deteleting any row.what i want is for
the above rows it should delete 3rd and 4th row because they are
lessthan and equal to every cell in 1st row.

2nd row can't be deleted because 2nd cell " 1 "geraterthan the 2nd cell
"0" of 1st row.

so ,plz help me

thanks
sree

sree,

Maybe I now understand ?
The code was comparing each cell to the cell just above it.
If you are saying that the comparison should be between each
cell and the cell in the top row, then maybe the following
will do what you want...

Jim Cone
San Francisco, USA
'--------------------------
Sub ab()
Dim i As Long
Dim lngWide As Long
Dim lngLastRow As Long
Dim lngStartRow As Long
Dim blnAlert As Boolean
Dim c As Excel.Range
Dim cell As Excel.Range
Dim CompareCells As Excel.Range

lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
lngStartRow = 2 ' Must be 2 or more
lngWide = 100 ' ?
Set CompareCells = _
Cells(lngStartRow - 1, 1).Resize(lngStartRow - 1, lngWide).Cells

For i = lngStartRow To lngLastRow
Set cell = Cells(i, 1).Resize(1, lngWide).Cells
For Each c In cell
If c.Value CompareCells(lngStartRow - 1, c.Column).Value Then
blnAlert = True
Exit For
End If
Next 'c

If Not blnAlert Then
cell.ClearContents
Else
blnAlert = False
End If
Next 'i

Set c = Nothing
Set cell = Nothing
Set CompareCells = Nothing
End Sub
'--------------------------


"sreedhar"
wrote in message

hi jim,
i have try changing the code to <=, = ,< but also it is not clearing
any rows even if it true.
i have try for rows

1 0 1 1
0 1 1 0
0 0 0 0
1 0 0 0

but the code is not clearing or deteleting any row.what i want is for
the above rows it should delete 3rd and 4th row because they are
lessthan and equal to every cell in 1st row.
2nd row can't be deleted because 2nd cell " 1 "geraterthan the 2nd cell
"0" of 1st row.
so ,plz help me
thanks
sree

hi jim,

it is working for first row thanks a lot ,

my problem is

after comparing with the first row it should then comparing with 2n
row with all other rows and delete.

after completion of 2nd row it should use 3rd row to compare with al
other rows
and delete.

like this it should follow all the rows till end

my idea for this is after clear any row we have call this marco to fil
up the empty blanks in the excel sheet.

Sub RemoveBlankRows()
Columns("A:A").SpecialCells(xlCellTypeBlanks).Dele te Shift:=xlUp

End Sub

and then agin go for other row comparsion and so on.


plz, help me regarding this

thank u
sre

--
sreedha
-----------------------------------------------------------------------
sreedhar's Profile: http://www.excelforum.com/member.php...fo&userid=2758
View this thread: http://www.excelforum.com/showthread.php?threadid=47453

sree,

The code is getting complicated.
The following may do what you want, however it is difficult
to determine if the correct rows are being deleted.
See how it works for you.
Note: change the "lngWide" setting to the correct number of columns.
I hope there are no more "additional" requirements.

'--------------------------
Sub ab()
Dim i As Long
Dim N As Long
Dim lngWide As Long
Dim lngLastRow As Long
Dim lngStartRow As Long
Dim blnAlert As Boolean
Dim c As Excel.Range
Dim cell As Excel.Range
Dim CompareCells As Excel.Range

lngStartRow = 2 ' Must be 2 or more
lngWide = 10 ' ?
Application.ScreenUpdating = False

Do 'Start a loop
lngLastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set CompareCells = _
Cells(lngStartRow - 1 + N, 1).Resize(1, lngWide).Cells

'Start another loop from the bottom up.
For i = lngLastRow To (lngStartRow + N) Step -1
Set cell = Cells(i, 1).Resize(1, lngWide).Cells

'Loop from left to right
For Each c In cell
If c.Value CompareCells(1, c.Column).Value Then
blnAlert = True
Exit For
End If
Next 'c

If Not blnAlert Then
cell.Delete shift:=xlUp 'Remove the row
Else
blnAlert = False
End If
Next 'i
N = N + 1
Loop Until N = (lngLastRow - lngStartRow - N)

Application.ScreenUpdating = True
Set c = Nothing
Set cell = Nothing
Set CompareCells = Nothing
End Sub
'--------------------------

Regards,
Jim Cone


"sreedhar"
wrote in message

hi jim,
it is working for first row thanks a lot ,
my problem is
after comparing with the first row it should then comparing with 2nd
row with all other rows and delete.
after completion of 2nd row it should use 3rd row to compare with all
other rows
and delete.
like this it should follow all the rows till end
my idea for this is after clear any row we have call this marco to fill
up the empty blanks in the excel sheet.

Sub RemoveBlankRows()
Columns("A:A").SpecialCells(xlCellTypeBlanks).Dele te Shift:=xlUp
End Sub

and then agin go for other row comparsion and so on.
plz, help me regarding this
thank u
sree

Of course if you had specified that in your original request, you wouldn't
be here now.

--
Regards,
Tom Ogilvy

"sreedhar" wrote in
message ...

hi,

i want to compare rows and delete the rows

previously i got an marco help from the group but it is comparing the
whole string
of the row

for example

it is comparing rows

1 0 1 0
1 0 1 1
0 1 0 0

for these rows the marco is deleting the thrid row because 1010 is
0100
but it shouldn't delete it because we have to compare by cell by cell
in the whole row

1 0 ,0 <1 ,1 0,0 = 0

but 0<1
so it should not delete the row.

the marco is as follows

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, 100)
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

but i need a marco which compares cell by cell in the whole row and
delete if all cells are than all cells in the other row else compare
with other row till end of xlsheet.

So, can any one plz help me A.S.P

thanks
sree


--
sreedhar
------------------------------------------------------------------------
sreedhar's Profile:
http://www.excelforum.com/member.php...o&userid=27582
View this thread: http://www.excelforum.com/showthread...hreadid=474533

hi jim,


thanks a lot it is working for some required results.

and lots of thanks for u'r help

once again thanks...........

sree


--
sreedhar
------------------------------------------------------------------------
sreedhar's Profile: http://www.excelforum.com/member.php...o&userid=27582
View this thread: http://www.excelforum.com/showthread...hreadid=474533