400 Excel macro error

Question

I am trying to run a macro that will delete rows that do not contain a specific value in column B. Here is 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 SHOULD do is the following:

1.) Find the number of lines to go through and set as the counter (this works)

2.) Start at line 21 and find “OSR Platform” and “IAM” in column B [this type of work (see below)]

3.) If it does not find any, delete the entire line and, if necessary, adjust the number and line number (this works)

For some reason, when the code falls into the first If statement, a window appears with a red X that simply says “400”. As far as I can tell, I wrote everything syntactically soundly, but obviously something is wrong.

Answer 1

You might want to start from a different path. When you delete a line, all previous lines are shifted. You take this into account, but the reverse loop is easier (for me anyway) to understand than to track when I shift the current position in the loop:

For i = count To 21 Step -1

Also, you rely 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 requires much more processing power, and depending on what you are trying to do, it may take significantly longer. Also for this proposed change, code size is reduced and it 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
• Independent version: count = Range("AF" & Rows.Count).End(xlUp).Row

Another thing you can do (and should do in this case) is to combine your If statements into one.

By making these changes, you will receive:

 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, you can go through the code line by line. Add a breakpoint and execute with F8 . Select the variables in your code, right-click, select "Add Watch ...", click "OK", ( here is a great resource that will help you with your debugging in general ) and pay attention to the following:

• Which line gets into the error?
• What is the value of i and count when this happens? (add hours to these variables to help)

Answer 2

It worked for me. It uses AutoFilter, does not require loops or sheet 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. First, check it on a copy of your book.

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