Home

Macros and Programming

 

CardboxForumsMacros and Programming > "macro to lookup data and delete row containing that data"

macro to lookup data and delete row containing that data

Current user: [none]
Register / Log In · Help

Posted By Post

RickStewart

24-Feb-2014 07:28

I have an Excel workbook with 24 sheets of data. The first sheet contains data in Column A that is bad data that needs to be deleted from all the other worksheets.

I will call the first worksheet with the bad data (1500 rows) "bad data"

Would like to record a macro that looks at data in A1 cell of "bad data" worksheet, finds matching data in all other worksheets, deletes that entire row of data, then also deletes the bad data in the original worksheet "bad data".

After delete row A in "bad data" worksheet, the bad data that WAS in A2 moves up to A1. would like to run the macro again using the new bad data that has been moved up to A1 as a result of previous deletions.

Or run a macro that looks up all data from column A in the first worksheet (1500 rows) and deletes all rows with matching data in the other 24 sheets.

The data is bad emails that i am deleting from my .csv email database file.

RickStewart

24-Feb-2014 07:46

this is what I got so far by recording my keystrokes. The problem is that the........Find(What:="gillespied@grantesd.k12.or.us"........the part in the quotes always changes.

I am willing to pay someone to write it for me.

 Selection.Copy
    Cells.Find(What:="gillespied@grantesd.k12.or.us", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Sheets("oregon").Select
    Cells.FindNext(After:=ActiveCell).Activate
    Rows("244:244").Select
    Application.CutCopyMode = False
    Selection.delete Shift:=xlUp
    Sheets("oregon").Select
    Selection.Find(What:="gillespied@grantesd.k12.or.us ", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Sheets("oregon").Select
    Selection.FindNext(After:=ActiveCell).Activate
    Sheets("bad emails").Select
    Cells.Find(What:="gillespied@grantesd.k12.or.us", After:=ActiveCell, _
        LookIn:=xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Activate
    Rows("1:1").Select
    Selection.delete Shift:=xlUp
End Sub

bert

24-Feb-2014 09:48

This is not an Excel macro list. However, perhaps is the solution simple:
Try to add as line 2:

emaddr = ActiveCell.Text
Then change the part in your line 2 : What:="gillespied@grantesd.k12.or.us" to What:=emadr

If you want to test first, add after the line emaddr = ActiveCell.Text
Msgbox emadr

If the Message box tells you what you want, remove the line.

I hope that 's all you need now.

You can pay me always and ever.

Regards
Bert

bert

24-Feb-2014 10:17

Excuse me: 2 write errors!
What:=emaddr
Msgbox emaddr

(double d)

Quick Reply

Please log in or register before trying to post a reply.

 
© 2010 Cardbox Software Limited   Home