Need help with Excel Macro for searching

[tom.jurewicz]

Hello,

I have been trying various methods for the past 2 days to do what I
need, but still no luck. Let me outline what it is I am trying to do.

1. I have a Excel workbook with 2 worksheets (AllUsers & Master)
The AllUsers sheet has the data that I want to modify/cleanup and the
Master is just a list of User ID's (A:A).

The data in AllUsers includes the User ID in column D.

What I am trying to do is create a macro that will search for all the
users in A:A of the Master sheet and delete the rows where those ID's
showup in AllUsers.

Any help is appreciated!!
Thanks!

 

[Guest]

Sub DeleteUsers()

MasterRowCount = 1
With Sheets("Master")
Do While .Range("A" & MasterRowCount) <> ""
UserID = .Range("A" & MasterRowCount)
With Sheets("AllUsers")
Set c = .Columns("D").Find(what:=UserID, _
LookIn:=xlValues)
If Not c Is Nothing Then
.Rows(c.Row).Delete
End If
End With
MasterRowCount = MasterRowCount + 1
Loop

End With
End Sub

 

[Bernie Deitrick]

Use a formula in column B or Master

=ISNUMBER(MATCH(A2,AllUsers!D,FALSE))

and copy down to match your list, then sort or filter on column B, and delete the rows where the
formula returns TRUE.

HTH,
Bernie
MS Excel MVP