VBA - row deletion variables

[dstepan]

Is it possible to delete multiple rows based on the following?

My original file is about 50,000 lines and each of the scenarios below could have hundreds of rows each.

1) If the first digit of the four digit number is a 1, 2, 3, 4, 5, or 9
2) If the 7 digit number is 6141 432
3)If the range of 7 digit numbers run from 7381 150 through 7381 179

For example:

Before
Column A Column B
1001 431
2004 180
6007 223
6141 432
7008 111
7299 199
7381 150
7381 180
9001 999

After
Column A Column B
6007 223
7008 111
7299 199
7381 180

 

[Joe4]

So, are there 4 numbers in column A and then 3 numbers in column B (you say a 7 digit number, but it really looks like a 4 and a 3 digit number)?

Also, are these values entered as numbers or text? An easy way to tell (provided you haven't selected a justification formatting option) is that text entries are typically left-justified within a cell, while numeric ones are right-justified.

 

[Fluff]

This is based on having a 4 digit number in column & a 3 digit number in B, starting in A1

Sub dstepan()

    Dim Rng As Range
    Dim Col As Range

    Set Col = Range("A1:A9")
    For Each Rng In Col
        If Rng Like "[1-5,9]*" Then Rng.Clear
        Select Case Rng.Value & Rng.Offset(, 1).Value
        Case Is = 6141432
            Rng.Clear
        Case 7381150 To 7381179
            Rng.Clear
        End Select
    Next Rng
    Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[dstepan]

Thank you for the help! this works splendidly. However, after the code is run I would like to also delete any non 4 digit number in column A.

Is this even possible?

 

[Fluff]

Do you just want to delete the number, or the entire row?

 

[dstepan]

I would like to delete the entire row.

 

[Fluff]

How about

Sub dstepan()

    Dim Rng As Range
    Dim Col As Range

    Set Col = Range("A1:A9")
    For Each Rng In Col
        [COLOR=#ff0000]If Len(Rng) <> 4 Then Rng.Clear[/COLOR]
        If Rng Like "[1-5,9]*" Then Rng.Clear
        Select Case Rng.Value & Rng.Offset(, 1).Value
        Case Is = "6141432"
            Rng.Clear
        Case 7381150 To 7381179
            Rng.Clear
        End Select
    Next Rng
    Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

Added the line in red