Deleting by Case Structure

[deboe]

Hey Guys,

I have a document in excel with colums A thru I. In column A I have some numbers that Identity the items in column C. So in column a is the identifying number and column c is the description. I want to delete those numbers in certain rows that I don't want but auto filter won't complete this task. This is the code that I have so far but it keeps giving me this error "Type mismatch error 13". Can someone check out my code and and let me know my mistakes?


Sub DeleteNonAmerican()

Dim cel As Range, AmerFnds

Set AmerFnds = Range("A1", Range("A1").End(xlDown))

For Each cel In AmerFnds

Select Case AmerFnds

Case 1101012 To 1115777, 1128217 To 1142391, 1355541 To 1355600, 1458906 To 1458977, 1462172 To 1462320, 1480006 To 1480007, 1480187 To 1480199, 1932853 To 1933049, 2096790 To 2096842, 2202620 To 2202643, 2344950 To 2345021, 2385379 To 2385391, 2385410 To 2385418, 2420428, 2420510 To 2420557, 2564447, 2564452 To 2564458, 2848750 To 2848765, 2854293 To 2854294, 2854330 To 2854338, 2824341 To 2854345, 2856870 To 2856893, 3355119, 3355121, 3355124 To 3355128, 3355133 To 3355134, 3355143, 3355145, 3381535 To 3381550, 3525468 To 3525469, 3525504 To 3525564, 3693521 To 3693526, 3785785 To 3786127

Case Else

Row.delete

End Select

Next

End Sub


Thanks in Advance

 

[SkipVought]

For Each [b]cel[/b] In AmerFnds

Select Case AmerFnds

    Case 1101012 To 1115777, 1128217 To 1142391, 1355541 To 1355600, 1458906 To 1458977, 1462172 To 1462320, 1480006 To 1480007, 1480187 To 1480199, 1932853 To 1933049, 2096790 To 2096842, 2202620 To 2202643, 2344950 To 2345021, 2385379 To 2385391, 2385410 To 2385418, 2420428, 2420510 To 2420557, 2564447, 2564452 To 2564458, 2848750 To 2848765, 2854293 To 2854294, 2854330 To 2854338, 2824341 To 2854345, 2856870 To 2856893, 3355119, 3355121, 3355124 To 3355128, 3355133 To 3355134, 3355143, 3355145, 3381535 To 3381550, 3525468 To 3525469, 3525504 To 3525564, 3693521 To 3693526, 3785785 To 3786127
    
    Case Else
        
        [b]Cel.Entire[/b]Row.delete
    
 End Select

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]

 

[deboe]

Anytime I run this Macro I keep getting the type mismatch error....Do you need me to send you the file so you can try it?

 

[SkipVought]

Sorry, I did not check all your code...

For Each cel In AmerFnds

Select Case [b]Cel.Value[/b]

    Case 1101012 To 1115777, 1128217 To 1142391, 1355541 To 1355600, 1458906 To 1458977, 1462172 To 1462320, 1480006 To 1480007, 1480187 To 1480199, 1932853 To 1933049, 2096790 To 2096842, 2202620 To 2202643, 2344950 To 2345021, 2385379 To 2385391, 2385410 To 2385418, 2420428, 2420510 To 2420557, 2564447, 2564452 To 2564458, 2848750 To 2848765, 2854293 To 2854294, 2854330 To 2854338, 2824341 To 2854345, 2856870 To 2856893, 3355119, 3355121, 3355124 To 3355128, 3355133 To 3355134, 3355143, 3355145, 3381535 To 3381550, 3525468 To 3525469, 3525504 To 3525564, 3693521 To 3693526, 3785785 To 3786127
    
    Case Else
        
        Cel.EntireRow.delete
    
 End Select

Skip,
[sub]
Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty![/sub]