Remove unnecessary rows

[Elton Law]

Dear expert,
Every day, will get a system generated file.
Have a list of names, duties, work .....

Sheet name is called Summary.
I have a sheet name called Work.
Is it possible if the names shown up in sheet "Work" will be retained, the
rest will be removed in sheet "Summary".

Name Type Time Shift Work
1129.HK PT 1 Half
0566.HK PT 2 Full
0395.HK FT, 3 Full
2302.HK PT, 4 Full
0260.HK PT, 5 Full
0757.HK PT, 6 Full
1088.HK PT, 7 Full
1171.HK PT, 1 Half
1393.HK FT, 2 Half
1898.HK PT, 3 Half
0090.HK FT, 5 Half
0735.HK PT, 3 Half
0091.HK PT, 5 Half
0639.HK PT, 2 Half
0712.HK PT, 1 Half

If sheet "work" has names of 1393.hk, 1129.hk, 0091.hk ..... only these 3
rows starting with these names in sheet "Summary" can be retained.
The rest can be deleted (no need) in sheet "Summary" after running the marco
.....

What shown up in sheet "Work" will determine what can be retained in Sheet
"Summary".

 

[JLGWhiz]

Give this a try:

Sub scrub()
Dim sh As Worksheet, sh2 As Worksheet
Dim lr As Long, lr2 As Long
Dim rng As Range, rng2 As Range
Set sh = Sheets("Work")
Set sh2 = Sheets("Summary")
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
Set rng2 = sh2.Range("A2:A" & lr2)
For c = lr2 To 2 Step -1
If Application.CountIf(rng, sh2.Cells(c, 1).Value) = 0 Then
sh2.Rows(c).Delete
End If
Next
End Sub

 

[Elton Law]

Hi friend, it's amazing job. It works. Thanks

Give this a try:

Sub scrub()
Dim sh As Worksheet, sh2 As Worksheet
Dim lr As Long, lr2 As Long
Dim rng As Range, rng2 As Range
Set sh = Sheets("Work")
Set sh2 = Sheets("Summary")
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row
Set rng = sh.Range("A2:A" & lr)
Set rng2 = sh2.Range("A2:A" & lr2)
For c = lr2 To 2 Step -1
If Application.CountIf(rng, sh2.Cells(c, 1).Value) = 0 Then
sh2.Rows(c).Delete
End If
Next
End Sub






.