Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 20th 07, 03:30 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 2,836
Default 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

  #2   Report Post  
Old May 20th 07, 12:54 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,302
Default 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



  #3   Report Post  
Old May 20th 07, 04:34 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Dec 2006
Posts: 2,836
Default 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






Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare 2 columns and delete duplicate text MF Excel Worksheet Functions 1 December 4th 09 07:13 PM
Compare cells and copy columns after match Kcope8302 Excel Worksheet Functions 2 August 5th 09 05:37 PM
compare rows for a match and sum the matches Bert Excel Worksheet Functions 1 April 30th 09 09:27 PM
How to compare two columns of data to make sure they match Len Excel Worksheet Functions 0 June 24th 08 04:07 PM
Want to compare two columns and then fill with data from the match Needy C Excel Worksheet Functions 1 January 11th 06 05:52 AM


All times are GMT +1. The time now is 09:49 AM.

Powered by vBulletin® Copyright ©2000 - 2021, Jelsoft Enterprises Ltd.
Copyright ©2004-2021 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017