Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove unnecessary rows
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". |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove unnecessary rows
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" wrote in message ... 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". |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Remove unnecessary rows
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" wrote in message ... 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". . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing unnecessary rows through macro | Excel Programming | |||
Delete Unnecessary Rows | Excel Discussion (Misc queries) | |||
"Add/Remove Rows Code" adds rows on grouped sheets, but won't remove rows. | Excel Programming | |||
How do I remove unnecessary vertical page break? | Excel Worksheet Functions | |||
Code / Add In to remove unnecessary white space | Excel Programming |