Oct 6, 2006
The situation is that I get a report on a daily basis with many account numbers included in it. I am only concerned with 22 of them. What I am trying to do is look at each account number and compare it to account numbers I have defined in the array. If the account is found then advance to the next row and begin the search again. If the account is not found in the array, then delete that row. Once I have told excel to delete the row, I also tell it to stay on that same row and continue the search. For instance if row 5 did not contain an account number in my array, then that row will be deleted, but since the row is deleted, row 6 just became row 5.This bit of code seems to also cause my code to stay in the loop.
View 6 Replies
I need to tell excel that if there is no more information to be evaluated, then exit the For Each statement. I donft want to exit the sub, because I need to add additional code, I just need it to exit the loop.
Dim lFailRows As Long
Dim Ary() As Variant
Dim x As Integer, AryIndex As Integer
Ary = Array(1843, 1844, 1845, 1847, 1906, 1907, 1940, 1967, 1982, 1983, 1984, _
1985, 1986, 1987, 2045, 2087, 2088, 2096, 2106, 2108, 2109, 2110)
lFailRows = ActiveWorkbook. Sheets(1). Range("A65536").End(xlUp).Row
For x = 2 To lFailRows
On Error Goto NotFound
For AryIndex = 0 To 21
If ActiveWorkbook.Sheets(1).Range("B" & x).Value = Ary(AryIndex) Then
ActiveWorkbook.Sheets(1).Range("B" & x).EntireRow.Delete
x = x - 1