Remove unnecessary rows
in [Programming]
|
From: Elton Law on 23 Apr 2010 11:30 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".
From: JLGWhiz on 23 Apr 2010 12:44 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" <EltonLaw(a)discussions.microsoft.com> wrote in message news:D158FDBB-9E2F-472B-851A-CE3591F58F04(a)microsoft.com... > 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". >
From: Elton Law on 24 Apr 2010 02:32
Hi friend, it's amazing job. It works. Thanks "JLGWhiz" wrote: > 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" <EltonLaw(a)discussions.microsoft.com> wrote in message > news:D158FDBB-9E2F-472B-851A-CE3591F58F04(a)microsoft.com... > > 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". > > > > > . > |