comparing rows and deletion

[sreedhar]

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

[Barb Reinhardt]

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" <sreedhar.1wm46b_1128823503.0786@excelforum-nospam.com> wrote in
message news:sreedhar.1wm46b_1128823503.0786@excelforum-nospam.com...
>
> 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
>

[Jim Cone]

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
news:sreedhar.1wm46b_1128823503.0786@excelforum-nospam.com
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]

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

[Jim Cone]

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
news:sreedhar.1wn1ic_1128866705.0876@excelforum-nospam.com
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

[sreedhar]

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

[Rowan Drummond]

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
>
>

[Jim Cone]

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
news:sreedhar.1wnw2d_1128906304.9889@excelforum-nospam.com
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

[sreedhar]

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).Delete Shift:=xlUp

End Sub

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


plz, help me regarding this

thank u
sree

[Jim Cone]

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
news:sreedhar.1wo76c_1128920703.5437@excelforum-nospam.com
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).Delete Shift:=xlUp
End Sub

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

[Tom Ogilvy]

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

--
Regards,
Tom Ogilvy

"sreedhar" <sreedhar.1wm46b_1128823503.0786@excelforum-nospam.com> wrote in
message news:sreedhar.1wm46b_1128823503.0786@excelforum-nospam.com...
>
> 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
>

[sreedhar]

hi jim,


thanks a lot it is working for some required results.

and lots of thanks for u'r help

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

sree