Macro to keep 15 specific records and delete 1000 other rows.

[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 a 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

 

[Don Guillett]

You don't say how many of each or if they are together or separated

1 a
1 b
2 a
4 b
8 c

 

[Bob Phillips]

As a starter

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = iLastRow To 1 Step -1
Select Case Cells(i, "A").Value
Case "value 1", "value 2", "value 3", _
"value 4", "value 5", "value 6", _
"value 7", "value 8", "value 9", _
"value 10", "value 11", "value 12", _
"value 13", "value 14", "value 15":
Case Else: Rows(i).Delete
End Select
Next i
Application.ScreenUpdating = True


End Sub

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Sam]

Hi Don,

I just wanted to keep any of the "part" numbers that came up in
"column" a. Pardon if I wasnt more specific. I guess the answer would
be any in only one column (together) .

Thank you so much Bob your solution was spot on. Jus to be curious I
tinkered with it a bit and heres what I ended up with. Also by turing
off the updating at the end caused it to run significantly faster? In
any case thank you so much for your help! Sam

Sub Test()
Dim iLastRow As Long
Dim i As Long

iLastRow = Cells(Rows.Count, "A").End(xlUp).Row
Application.ScreenUpdating = False
For i = iLastRow To 1 Step -1
Select Case Cells(i, "A").Value
Case "1", "2", "3", "4", "5", "6", "7", "8", "9", "10",
"11", "12", "13", "14", "15": _
Case Else: Rows(i).Delete
End Select
Next i
Application.ScreenUpdating = False


End Sub

 

[Bob Phillips]

That doesn't make any sense Sam, all the work is done by then. Plus, you
might be messing other stuff up by not getting screen updating back.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Don Guillett]

Either this OR the commented lines will work with the info given.
However, I suspect the info given is not really the case. If you turn off
updating, turn it on again when finished.

Sub test3()
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "a") > 15 Then Rows(i).Delete
'Select Case Cells(i, "A").Value
'Case Is > 15: Rows(i).Delete
'End Select
Next i
End Sub

 

[Tom Ogilvy]

That doesn't make any sense Sam, all the work is done by then.

re: Application.ScreenUpdating = False
Not the first time he calls it. (which is above the start of the loop) -
maybe you missed it.

The second time should be set to true rather than false, but is immaterial
as it is turned back on by default it this is run independently which I am
sure it is.