Macro to keep 15 specific records and delete everything else.

[Sam]

Hi Gang,

I work inventory control for a company in Texas and have to check stock

levels for around 15 parts daily. The download for these parts contain
an additional 1000 other rows of part numbers I dont need. I have used
Excel macros in the past that delete rows based on specific criteria
but how
do I do the reverse? Essentially use an Excel macro that says "keep
these 15
specific part numbers" but delete everything else that isnt them. I
posted this question mistakenly in an Access forum so my aplogies if
this looks like a double post.


Your help is greatly appreciated.


Sam

 

[Dave Peterson]

You have an active thread elsewhere.

Hi Gang,

I work inventory control for a company in Texas and have to check stock

levels for around 15 parts daily. The download for these parts contain
an additional 1000 other rows of part numbers I dont need. I have used
Excel macros in the past that delete rows based on specific criteria
but how
do I do the reverse? Essentially use an Excel macro that says "keep
these 15
specific part numbers" but delete everything else that isnt them. I
posted this question mistakenly in an Access forum so my aplogies if
this looks like a double post.

Your help is greatly appreciated.

Sam

 

[jkend69315]

Sam, you could use a select case statement. I'm not sure how many
expressions are allowed in each case statement, so I just broke them up
into sets of 5 each. I also assumed your part numbers were in column A
and I used simple integers for the part numbers. HTH, James

Sub DelUnneededRows()
Dim k As Long
For k = Cells(65536, "a").End(xlUp).Row To 2 Step -1
Select Case Cells(k, "a")
Case 1, 2, 3, 4, 5
'do nothing
Case 6, 7, 8, 9, 10
'do nothing
Case 11, 12, 13, 14, 15
'do nothing
Case Else
Rows(k).EntireRow.Delete
End Select
Next k
End Sub

 

[Sam]

Thank you so much James, it worked like a champ, at first I was a bit
dismayed by how long it took to run but when I pared it down to
searching only 1700 rows (cus thats only as long in the range the macro
has to search) and turned the updating off it reduced the time
searching from 10 seconds to under 2. Also cus I was curious I tried
putting everything in one case statement and it worked.

Thanks again. Sam

Sub DelUnneededRows1()
Application.ScreenUpdating = False
Dim k As Long
For k = Cells(1700, "a").End(xlUp).Row To 2 Step -1
Select Case Cells(k, "a")
Case 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15
Case Else
Rows(k).EntireRow.Delete
End Select
Next k
End Sub