Macro to keep 15 specific records and delete everything else.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Sam - 22 Oct 2006 15:53 GMT

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

Reply to this Message

Dave Peterson - 22 Oct 2006 16:01 GMT

You have an active thread elsewhere.

> Hi Gang,
>
[quoted text clipped - 13 lines]
>
> Sam

Signature

Dave Peterson

Reply to this Message

jkend69315@aol.com - 22 Oct 2006 16:10 GMT

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

> Hi Gang,
>
[quoted text clipped - 13 lines]
>
> Sam

Reply to this Message

Sam - 22 Oct 2006 19:04 GMT

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

> 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
[quoted text clipped - 34 lines]
> >
> > Sam

Reply to this Message

Rate this thread: