Macro to find and delete records by multiple column data

[Kelsey]

Hello,
I'm trying to figure out a way to write a macro that finds a specific first name, last name and address and then deletes the row that contains this data. I've recorded a macro that does this, but it obviously has some problems. For example, it only searches by one of these three columns (first, last, address) so there is the potential that it will delete records that share the same first name. It also uses static cell references, so if I try to use it in other files it will delete those cells instead of the cells it found.

Below is the macro that I recorded, if that is any help. Also, if it's possible to find based on all of the fields in the document, that would be even better. I've attached a sample document. What I'd want it to do is find Cassidy, Jo at 808 Yellowstone Rd Nowhere, MS 1234-5680 and delete that entire row, then find Delores, Tate at 9083 Catbird Ct Somwhere, OH 1234-5681 and delete that entire row; then find Innis, Fred at 7493 Bottle Cir Pond, AK 1234-5686 and delete the entire row.

If you can assist me or point me in the right direction it would be greatly appreciated!!

 

[Kelsey]

Sorry, I forgot to add my recorded macro. Please see below.

Sub DeleteSeeds()
'
' DeleteSeeds Macro
'

'
    Cells.Find(What:="fraga", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Cells.FindNext(After:=ActiveCell).Activate
    Rows("8233:8244").Select
    Selection.Delete Shift:=xlUp
    Range("C8235").Select
    Cells.Find(What:="murray", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    ActiveWindow.SmallScroll Down:=10
    Rows("8243:8254").Select
    Selection.Delete Shift:=xlUp
    Range("E8246").Select
    Cells.Find(What:="resseger", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    Rows("8248:8248").Select
    ActiveWindow.SmallScroll Down:=9
    Rows("8248:8259").Select
    Selection.Delete Shift:=xlUp
    Range("C8249").Select
End Sub

 

[dan_l]

How big are the two lists you have?

 

[Kelsey]

The multiple columns are in the same worksheet, there are around 6,000 rows. The amount of data will vary, though, because I'd like to be able to use this with different workbooks. Typically it's anywhere from 6,000 to 15,000 rows, but I only need to find three specific people who are in there 12 times, so 36 total rows that need found and deleted.

 

[dan_l]

Well here's 2 ways that come to mind:

-Suck all of your data into an array, write records that aren't flagged as to delete into a new array, put the new array out into a new workbook.

-Use access.


The latter of which would take you about 5 minutes to set up....

 

[Marc L]

Hi,

another way is to use filters …
​

 

[Gangadhar Yeligaty]

Hi,

Please find attached macro file which will find the records with given matching values and delete the records.

Regards,
Gangadhar Yeligaty

 

[Kelsey]

Thanks for all of your responses and help!

Gangadhar, I'm going to give that Macro a try. Thanks again!

 

[Kelsey]

Hi Gangadhar, I'm having trouble getting this macro to work with the document I'm using. I can see that it works with the test document, but it isn't functioning properly in the other document. Could it be because the other document has more rows and columns than the test document?

Thanks for your help!

 

[Gangadhar Yeligaty]

Hi,

Please find attached updated Macro file which will work on the Test and other documents as well.

Note: Make sure that the first 8 columns should be there in the same order as Test document.

I hope this works fine.

Regards,
Gangadhar Yeligaty

 

[Kelsey]

Hi Gangadhar,
I tried using this macro and it still isn't deleting the records. The first 8 columns are in the same order, so it looks like the values in the formula are correct (which, I assume, is where you piece together the information that it needs to find).

Could the problem be that there are multiple lines of these names and information? Also, if one of the people it searches for isn't there, will that cause it not to work?

I'm sorry, it seems this is becoming a hassle. I very much appreciate your help and if you are unable to help me further I understand.

Thanks!
Kelsey