Deleting MANY Rows

Chrismx222

New Member
Joined
Apr 18, 2008
Messages
12
I have a sheet of 15,000 rows. Each row contains a different sku number.
When I'm done editing/deleting the whole sheet I only want to show certain sku numbers.
The following example is of the sheet UN edited:
<table style="border-collapse: collapse; width: 167pt;" width="222" border="0" cellpadding="0" cellspacing="0"><col style="width: 167pt;" width="222"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt; width: 167pt;" width="222" height="20">5011-AB-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-AB-MED-CSG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-AC-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-AC-MED-CSG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-DAB-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-DAB-MED-CSG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-DAC-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-DAC-MED-CSG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-DB-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-DB-MED-CSG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-RB-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-RB-MED-CSG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-RC-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-RC-MED-CSG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-VG-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5011-VG-MED-CSG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-AB-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-AB-MED-FST</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-AC-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-AC-MED-FST</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-DAB-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-DAB-MED-FST</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-DAC-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-DAC-MED-FST</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-DB-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-DB-MED-SMG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-RB-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-RB-MED-SMG</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-RC-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-RC-MED-FST</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-VG-MED-CLR</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl67" style="height: 15pt;" height="20">5012-VG-MED-CSG</td> </tr> </tbody></table>
When done I want it to look like this:
<table style="border-collapse: collapse; width: 167pt;" width="222" border="0" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 15pt;" height="20"></tr><tr style="height: 15pt;" height="20"><td class="xl67" style="height: 15pt; width: 167pt;" width="222" height="20">5011-AB-MED-CLR</td></tr><tr style="height: 15pt;" height="20"><td class="xl67" style="height: 15pt;" height="20">5011-AB-MED-CSG</td></tr></tbody> </table> <table style="border-collapse: collapse; width: 167pt;" width="222" border="0" cellpadding="0" cellspacing="0"><tbody><tr style="height: 15pt;" height="20"><td class="xl67" style="height: 15pt;" height="20">5012-AB-MED-CLR</td></tr><tr style="height: 15pt;" height="20"><td class="xl67" style="height: 15pt;" height="20">5012-AB-MED-FST

</td></tr></tbody> </table>As you can see I only want to keep the 'AB' items, but some of the 'AB' items have different beginning sku numbers and some have different proceeding sku numbers. I would greatly appreciate help with this as I'm deleting the rows in between the 'AB's but this is taking a lot of time. Maybe some sort of Macro, of which I'm not sure how to write. Thank you again.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
I would use a custom auto filter with the criteria "Does not contain" -AB-

Then delete everything that remains.

Gary
 

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try this

Code:
Sub DelNotAB()
Dim LR As Long, i As Long
Application.ScreenUpdating = False
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = LR To 1 Step -1
    With Range("A" & i)
        If Mid(.Value, 6, 2) <> "AB" Then .EntireRow.Delete
    End With
Next i
Application.ScreenUpdating = True
End Sub
 

amylex

New Member
Joined
Dec 11, 2007
Messages
18
I'm sure someone will come along with a better answer, but when I have to do something similar I do a Find/Replace. Since you can use wildcards it should look like:

****-DB-***-***
or

****-DAC-***-***


Replace with should stay blank. Since the AB's are what you want to keep, all other combinations will be removed. Hopefully there aren't too many different combinations - so it shouldn't take too long.

I would then sort and let the empty cells sort out to the end. It's easy then to mass select the rows for deletion.

I know this probably isn't the correct way to do this, but I understand how deadlines are :)
 

Chrismx222

New Member
Joined
Apr 18, 2008
Messages
12
I would use a custom auto filter with the criteria "Does not contain" -AB-

Then delete everything that remains.

Gary


Gary this was the answer. Something so simple, just a click of a button, I didn't know. Thanks for pointing this tool out! Saved me SOOOO much time. Thanks, Chris
 

Gary McMaster

Well-known Member
Joined
Feb 8, 2009
Messages
1,977
I'm glad it worked for you.

What I usually do in a case like this is use "Contains". Then you can just copy what is displayed to another sheet. Why delete data only to find that you need it later?

Gary
 

Watch MrExcel Video

Forum statistics

Threads
1,122,518
Messages
5,596,622
Members
414,082
Latest member
sasmita

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top