Macro to check columns and delete rows

May 15, 2009 at 09:20:44
Specs: Windows XP
Hi,

This is the first time i have ever posted and hope someone can help. I have a workbook with 2 sheets, in sheet 1, column a, I have a list of references (usually a combo of numbers and letters) and in Sheet 2 I have a table of items, I need a macro that will look at column R and Column V of the table to find a match against column A of spreadsheet 1, if it finds a match I would like to keep that row, if it doesn't I would like it to delete the row, any ideas?


See More: Macro to check columns and delete rows

Report •


#1
May 15, 2009 at 12:46:36
This thread covers a similar issue and the code suggested could be modified to fit your needs:

http://www.computing.net/answers/of...


Report •

#2
May 15, 2009 at 13:36:29
Thanks, I did see that post, unfortunately I can't figure out how to modify it to look at 2nd column without deleting what it found in the first one. Any help would be appreciated.

Thanks


Report •

#3
May 15, 2009 at 16:31:04
If I assume that the data you are looking for will only be in Columns R and/or V, then I would alter the macro to search the entire range R1:V(lastRow).

If the data could be found in Columns S:U, but you don't want to delete the row if found there, let me know.

Sub DeleteIDs()
'Assume Sheet1 has long list, Sheet2 has short list
'Find last piece of data in Sheet1 Column A
  LastRow = Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
'Start at bottom of Column A and get ID
   For NxtID = LastRow To 1 Step -1
    ID = Sheets(1).Cells(NxtID, "A")
'Look for ID in Columns R:V of Sheet2
     With Sheets(2).Range("R1:V" & LastRow)
      Set c = .Find(ID, lookat:=xlWhole)
'Delete row in Sheet1 if ID not found on Sheet2
       If c Is Nothing Then Sheets(1).Cells(NxtID, "A").EntireRow.Delete
     End With
'Move up 1 row on Sheet1 and check the next ID
   Next
End Sub


Report •

Related Solutions

#4
May 15, 2009 at 16:35:11
For only Columns R and/or V, use:

With Sheets(2).Range("R1:R" & LastRow & ",V1:V" & LastRow)


Report •

#5
May 16, 2009 at 00:45:44
Thanks for your reply, I tried putting the macro in and all it did was delete column A from sheet 1, what I am looking for is it to find data in Column A, sheet 1 and see if it finds a match in the table on Sheet 2, in either Column R or V, if it doesn't find a match in either of those 2 columns I would like it to delete the row in Sheet 2.

Cheers


Report •

#6
May 16, 2009 at 13:43:06
OK, your last post doesn't make sense.

If we go back to the beginning of this thread, I read it to mean this:

- You have a list data in Sheet1!A:A.
- You want to look for each piece of data in that list and compare to the data in columns R & V on Sheet2.
- If the data does not exist in those columns, then delete that row in Sheet1.

Apparently that is not what you want to do, but I don't understand your last post.

You said "if it doesn't find a match in either of those 2 columns I would like it to delete the row in Sheet 2."

If it doesn't find a match, what row do you want to delete?

In other words, if AAAA exists in Sheet1!A:A, but not in column R or V on Sheet2, what row should be deleted? All of them? I doubt it.



Report •

#7
May 17, 2009 at 09:35:29
Sorry if my post wasn't descriptive enough, I managed to get it sorted, thanks for your help, here was my final solution.

Sub DeleteIDs()
Dim Found1 As Range, Found2 As Range
Dim LR As Long, i As Long
With Sheets("Sheet2")
LR = .Range("R" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
Set Found1 = Sheets("Sheet1").Columns("A").Find(what:=.Range("R" & i).Value, LookIn:=xlValues, lookat:=xlWhole)
Set Found2 = Sheets("Sheet1").Columns("A").Find(what:=.Range("V" & i).Value, LookIn:=xlValues, lookat:=xlWhole)
If Found1 Is Nothing And Found2 Is Nothing Then .Rows(i).Delete
Next i
End With
End Sub


Report •

#8
May 17, 2009 at 10:09:29
Sorry if my post wasn't descriptive enough, I managed to get it sorted, thanks for your help, here was my final solution.

Sub DeleteIDs()
Dim Found1 As Range, Found2 As Range
Dim LR As Long, i As Long
With Sheets("Sheet2")
LR = .Range("R" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
Set Found1 = Sheets("Sheet1").Columns("A").Find(what:=.Range("R" & i).Value, LookIn:=xlValues, lookat:=xlWhole)
Set Found2 = Sheets("Sheet1").Columns("A").Find(what:=.Range("V" & i).Value, LookIn:=xlValues, lookat:=xlWhole)
If Found1 Is Nothing And Found2 Is Nothing Then .Rows(i).Delete
Next i
End With
End Sub


Report •


Ask Question