400 Error Excel Macro

Question

I'm trying to run a macro that will delete rows that don't contain a particular value in column B. Here's my code:

Sub deleteRows()
    Dim count As Integer
    count = Application.WorksheetFunction.CountA(Range("AF:AF"))
    Dim i As Integer
    i = 21
    Do While i <= count
        If (Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("OSR Platform", Range("B" & i))) = False) Then
            If (Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("IAM", Range("B" & i))) = False) Then
                Rows(i).EntireRow.Delete
                i = i - 1
                count = count - 1
            End If
        End If
        i = i + 1
    Loop
End Sub

Now what it SHOULD be doing is the following:

1.) Find the number of rows to go through and set that as count (this works)

2.) Start at row 21 and look for "OSR Platform" and "IAM" in column B [this kind of works (see below)]

3.) If it finds neither, delete the entire row and adjust the count and row number as necessary (this works)

For some reason, whenever the code gets to the first If statement, an error window with a red X pops up that just says "400." As far as I can tell, I have written everything syntactically soundly, but clearly there's something wrong.

Answer 1

You may want to start by looping the other way. When you delete a line, all the previous lines are shifted. You account for this, but a reverse loop is simpler (for me anyways) to understand than keeping track of when I've offset the current position within the loop:

For i = count To 21 Step -1

Also, you're relying too much on Application.WorksheetFunction:

(Application.WorksheetFunction.IsNumber(Application.WorksheetFunction.Search("OSR Platform", Range("B" & i))) = False)

to

InStr(Range("B" & i).value, "OSR Platform") > 0

Application.WorksheetFunction takes much more processing power, and depending on what you are trying to accomplish, this can take a significantly longer amount of time. Also for this suggested change, the code size is reduced and becomes easier to read without it.

Your count can also be obtained without A.WF:

• Excel 2000/03: count = Range("AF65536").End(xlUp).Row
• Excel 2007/10: count = Range("AF1048576").End(xlUp).Row
• Version independent: count = Range("AF" & Rows.Count).End(xlUp).Row

One more thing is that you can do (and should do in this case) is combine your If statements into one.

Making these changes, you end up with:

Sub deleteRows()
    Dim count As Integer
    count = Range("AF" & Rows.Count).End(xlUp).Row
    Dim i As Integer
    For i = count To 21 Step -1
        If Len(Range("B" & i).value) > 0 Then
            If InStr(Range("B" & i).value, "OSR Platform") > 0 Or InStr(Range("B" & i).value, "IAM") > 0 Then
                Range("B" & i).Interior.Color = RGB(255, 0, 0)
            End If
        End If
    Next i
End Sub

If this does not help, then can you step through the code line by line. Add a breakpoint, and step through with F8. Highlight the variables in your code, right-click, choose "add Watch...", click "OK", (Here's an excellent resource to help you with your debugging in general) and note the following:

• Which line hits the error?
• What is the value of i and count when that happens? (add a watch on these variables to help)

Answer 2

This worked for me. It uses AutoFilter, does not require looping or worksheet functions.

Sub DeleteRows()

Dim currentSheet As Excel.Worksheet
Dim rngfilter As Excel.Range
Dim lastrow As Long, lastcolumn As Long

Set currentSheet = ActiveSheet

' get range
lastrow = currentSheet.Cells(Excel.Rows.Count, "AF").End(xlUp).Row
lastcolumn = currentSheet.Cells(1, Excel.Columns.Count).End(xlToLeft).Column
Set rngfilter = currentSheet.Range("A1", currentSheet.Cells(lastrow, lastcolumn))

' filter by column B criteria
rngfilter.AutoFilter Field:=2, Criteria1:="<>*OSR Platform*", Operator:= _
        xlAnd, Criteria2:="<>*IAM*"

' delete any visible row greater than row 21 which does not meet above criteria
rngfilter.Offset(21).SpecialCells(xlCellTypeVisible).EntireRow.Delete

' remove autofilter arrows
currentSheet.AutoFilterMode = False
End Sub

This code applies AutoFilter to column B to see which rows contain neither "OSR Platform" nor "IAM" in column B. Then it simply deletes the remaining rows greater than 21. Test it on a copy of your workbook first.

With a huge nod to this OzGrid thread, because I can never remember the proper syntax for selecting visible cells after filtering.