Deleting MANY Rows

Chrismx222

New Member
Joined
Apr 18, 2008
Messages
14
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.
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
I would use a custom auto filter with the criteria "Does not contain" -AB-

Then delete everything that remains.

Gary
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,316
Members
448,564
Latest member
ED38

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
Back
Top