VBA Delete rows based on array?

[Hendrixx]

Hi all,

Newbie here.

I have a list of 16 9-digit numbers.

I have a worksheet with approx 1,000 9-digit numbers in Column A and other data in other columns.
I would like to use VBA to delete all rows in which one of the 16 9-digit numbers is in Column A.
I thought of placing the 16 9-digit numbers in an array and deleting rows based on comparing Column A with this array but can’t work out how to do it
Is this possible?
I have searched around and can’t find exactly what I’m looking for, and have had some success in deleting single rows, but not all of them.

I can’t post my code as I’m currently in the train….but I’m thinking I’ll have to start from scratch again anyway.

 

[Rick Rothstein]

Will the 16 9-digit numbers always be the same and never change over time?

 

[Hendrixx]

Will the 16 9-digit numbers always be the same and never change over time?

Hi Rick,

yes they remain the same.

 

[Rick Rothstein]

This should do what you want (replace the 16 test numbers I used with your actual 9-digit numbers)...

Sub RemoveRowMatchingNumbers()
  Dim Num As Variant
  For Each Num In Array(111111111, 222222222, 333333333, 444444444, _
                        555555555, 666666666, 777777777, 888888888, _
                        999999999, 121212121, 343434343, 565656565, _
                        787878787, 898989898, 123123123, 987987987)
    Columns("A").Replace Num, "", xlWhole, , , , False, False
  Next
  Columns("A").SpecialCells(xlBlanks).EntireRow.Delete
End Sub

 

[Hendrixx]

Wow, that looks much simpler than whatever it was that I was doing.

Thanks Rick. I'll give it a try and report back with a "Mark as solution".

I appreciate your help.

 

[Hendrixx]

Worked beautifully. Thank you Rick. Marked as solved.