VBA Excel Macro HELP!!!

[tjamesp]

Hi,

I am trying to write a macro in Excel 2003 that will sort some data and delete the unwanted rows. Basically, starting on line 3, in columns C to L, if the charater strings "N" or "NI" DO NOT appear, I want to delete the row. The number of total rows will vary. I am getting an Else without If error. Here is my code. By the way, I am new to programming so the help is much appreciated.

Oh, and the Macro should exit on the first blank row.

Sub FilterPositives()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Report")

iRow = ws.Cells(Rows.Count, 10).End(xlUp).Offset(2, 3).Row

If ws.Cells(iRow, 1).Value = "" Then MsgBox "Report Complete"

ElseIf ws.Cells(iRow, 2).Value = "N" Or ws.Cells(iRow, 1).Value = "NI" Then
Next iRow
ElseIf ws.Cells(iRow, 2).Value = "N" Or ws.Cells(iRow, 1).Value = "NI" Then
Next iRow
ElseIf ws.Cells(iRow, 3).Value = "N" Or ws.Cells(iRow, 1).Value = "NI" Then
Next iRow
ElseIf ws.Cells(iRow, 4).Value = "N" Or ws.Cells(iRow, 1).Value = "NI" Then
Next iRow
ElseIf ws.Cells(iRow, 5).Value = "N" Or ws.Cells(iRow, 1).Value = "NI" Then
Next iRow
ElseIf ws.Cells(iRow, 6).Value = "N" Or ws.Cells(iRow, 1).Value = "NI" Then
Next iRow
ElseIf ws.Cells(iRow, 7).Value = "N" Or ws.Cells(iRow, 1).Value = "NI" Then
Next iRow
ElseIf ws.Cells(iRow, 8).Value = "N" Or ws.Cells(iRow, 1).Value = "NI" Then
Next iRow
ElseIf ws.Cells(iRow, 9).Value = "N" Or ws.Cells(iRow, 1).Value = "NI" Then
Next iRow
ElseIf ws.Cells(iRow, 10).Value = "N" Or ws.Cells(iRow, 1).Value = "NI" Then
Next iRow
Else: ActiveCell.Row.Select
    Selection.Delete
    Next iRow

End If

[ChrisPadgham]

Just at a glance I think you should move msgbox "Report Complete" to the next line. Generally speaking in VB if you have it on one line it is viewed as a complete statement hence the first else if is not considered part the statement.

Alternatively move the ElseIF to the end of the row after "Report Complete"

[hectop]

You can minimize the coding with a second loop.
It wasn't clear if the value in the cell was only N or NI or something else, so I just checked the 1st and 1st 2 characters. You can use whatever logic you need.

Think this will do the trick

Sub filterpos()
    Dim ws As Worksheet
    Dim iRow As Integer
    Set ws = Worksheets("Report1")
    iRow = 3

    Do While True
        If ws.Cells(iRow, 1) = vbNullString Then Exit Do
        If Left(ws.Cells(iRow, 1), 1) <> "N" And Left(ws.Cells(iRow, 1), 2) <> "NI" Then
            ws.Cells(iRow, 1).EntireRow.Delete
        Else
            iRow = iRow + 1
        End If

    Loop
    MsgBox ("Report Complete")

End Sub