Finding a cell match then deleting a row.
 

Greetings I did a search and found things close to what I want but I have not
been able to adapt them for my purpose successfully.

I have a sheet called 'Tables' with a one cell named range called 'Emphold'
An employees name gets copied into Emphold from a table of names elsewhere.

I have a sheet called 'Employee Database' with employee names in column A.

Via a macro I want to use the name that is in Emphold and search for an
exact match column A of the Employee Database sheet. If I find a match I
want to delete the entire row of the found name.

(This is to automate the deletion of an employee from the Employee Database
sheet.)

Finding a cell match then deleting a row.
 

Hi Tim:

This code assumes that you have already defined the Name. It scans the used
rows and removes any rows with a match to the contents of Emphold:


Sub dropem()
Dim s As String
Dim r, emp As Range
Set emp = Range("Emphold")
s = emp.Value

Worksheets("Employee Database").Activate
Set r = ActiveSheet.UsedRange
j = r.Rows.Count + r.Row + 1

For i = j To 1 Step -1
If Cells(i, 1).Value = s Then
Rows(i).EntireRow.Delete
End If
Next
End Sub
--
Gary's Student


"Tim M" wrote:

Greetings I did a search and found things close to what I want but I have not
been able to adapt them for my purpose successfully.

I have a sheet called 'Tables' with a one cell named range called 'Emphold'
An employees name gets copied into Emphold from a table of names elsewhere.

I have a sheet called 'Employee Database' with employee names in column A.

Via a macro I want to use the name that is in Emphold and search for an
exact match column A of the Employee Database sheet. If I find a match I
want to delete the entire row of the found name.

(This is to automate the deletion of an employee from the Employee Database
sheet.)

Finding a cell match then deleting a row.
 

Thanks you very much, that worked out well. I just added a wee bit of code
to the front that actually copies the name to the hold cell and all is well.

"Gary''s Student" wrote:

Hi Tim:

This code assumes that you have already defined the Name. It scans the used
rows and removes any rows with a match to the contents of Emphold:


Sub dropem()
Dim s As String
Dim r, emp As Range
Set emp = Range("Emphold")
s = emp.Value

Worksheets("Employee Database").Activate
Set r = ActiveSheet.UsedRange
j = r.Rows.Count + r.Row + 1

For i = j To 1 Step -1
If Cells(i, 1).Value = s Then
Rows(i).EntireRow.Delete
End If
Next
End Sub
--
Gary's Student


"Tim M" wrote:

Greetings I did a search and found things close to what I want but I have not
been able to adapt them for my purpose successfully.

I have a sheet called 'Tables' with a one cell named range called 'Emphold'
An employees name gets copied into Emphold from a table of names elsewhere.

I have a sheet called 'Employee Database' with employee names in column A.

Via a macro I want to use the name that is in Emphold and search for an
exact match column A of the Employee Database sheet. If I find a match I
want to delete the entire row of the found name.

(This is to automate the deletion of an employee from the Employee Database
sheet.)