ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Compare columns, delete the rows that DO match. (https://www.excelbanter.com/excel-programming/389704-compare-columns-delete-rows-do-match.html)

ryguy7272 May 20th 07 03:30 AM

Compare columns, delete the rows that DO match.
 
The following code was posted to the programming DG a while back:

Sub Redundancy()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If IsError(Application.Match(Cells(i, "A").Value, _
Range("B:B"), 0)) Then
Cells(i, "A").Delete Shift:=xlUp
End If
Next i

End Sub

I was playing with it and trying to get it to delete the rows (in column
'A') that DO match. It should be fairly simple, but I just can’t figure it
out right now. I thought I could keep the code mostly the same, but because
of the nature of the Match function, I am beginning to think that it may
require something radically different. Can someone please offer some
guidance?
TIA!!

--
RyGuy

Norman Jones May 20th 07 12:54 PM

Compare columns, delete the rows that DO match.
 
Hi RyGuy,

Try changing:

If IsError(Application.Match(Cells(i, "A").Value, _


to

If Not IsError(Application.Match(Cells(i, "A").Value, _


---
Regards,
Norman


"ryguy7272" wrote in message
...
The following code was posted to the programming DG a while back:

Sub Redundancy()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If IsError(Application.Match(Cells(i, "A").Value, _
Range("B:B"), 0)) Then
Cells(i, "A").Delete Shift:=xlUp
End If
Next i

End Sub

I was playing with it and trying to get it to delete the rows (in column
'A') that DO match. It should be fairly simple, but I just can't figure
it
out right now. I thought I could keep the code mostly the same, but
because
of the nature of the Match function, I am beginning to think that it may
require something radically different. Can someone please offer some
guidance?
TIA!!

--
RyGuy




ryguy7272 May 20th 07 04:34 PM

Compare columns, delete the rows that DO match.
 
Don't know why I didn't see that before.......
Yet another lesson learned!!!

--
RyGuy


"Norman Jones" wrote:

Hi RyGuy,

Try changing:

If IsError(Application.Match(Cells(i, "A").Value, _


to

If Not IsError(Application.Match(Cells(i, "A").Value, _


---
Regards,
Norman


"ryguy7272" wrote in message
...
The following code was posted to the programming DG a while back:

Sub Redundancy()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = iLastRow To 2 Step -1
If IsError(Application.Match(Cells(i, "A").Value, _
Range("B:B"), 0)) Then
Cells(i, "A").Delete Shift:=xlUp
End If
Next i

End Sub

I was playing with it and trying to get it to delete the rows (in column
'A') that DO match. It should be fairly simple, but I just can't figure
it
out right now. I thought I could keep the code mostly the same, but
because
of the nature of the Match function, I am beginning to think that it may
require something radically different. Can someone please offer some
guidance?
TIA!!

--
RyGuy






All times are GMT +1. The time now is 03:12 PM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
ExcelBanter.com