VBA to delete criteria is deleting my first row regardless of criteria.

[jmazorra]

Hello folks

I worked out a function and a procedure to help me find the last row and then delete filtered criteria. If there is not criteria, then leave the data alone. However, the procedure does filter and delete the data based on the criteria, but if there is no criteria instead of leaving the row alone, it deletes my first row.

Here is my function

Public Function GETLASTROW(ByVal rngToCheck As Range) As Long

'This function is used by all procedures where row values are deleted depeding
'on a certain value or combination of values.
'The function is called to determine the last populated row of a specified range:


Dim rngLast As Range
    
    Set rngLast = rngToCheck.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
    
        If rngLast Is Nothing Then
            GETLASTROW = rngToCheck.Row
        
                Else
                 GETLASTROW = rngLast.Row
                 
                    End If
                    
                    
    
End Function

Here is my procedure

Sub last_row()

Dim lngLastRow As Long


    Application.ScreenUpdating = False
    
    With Sheets("New Leaves Report")
        
        lngLastRow = GETLASTROW(.Cells)
        
        If lngLastRow > 1 Then
            'we don't want to delete our header row
            With .Range("B2:B" & lngLastRow)
            
                    .AutoFilter Field:=1, Criteria1:="#N/A"
                    .EntireRow.delete
                    
            End With
        End If
    End With
    
    Application.ScreenUpdating = True
    
    
End Sub

I can't figure out why is deleting row B2 even if there is no criteria to delete. Any help is appreciated.

 

[Fluff]

The filter needs to go in the header row.
Is column B formulae?

 

[jmazorra]

The filter needs to go in the header row.
Is column B formulae?

Yes, column B is formulas. My data begins on B2, row B1 is my headers.

 

[Fluff]

Try

Sub DelRows()
On Error Resume Next
Range("B:B").SpecialCells(xlFormulas, xlErrors).EntireRow.Delete
On Error GoTo 0
End Sub

This will delete any row that has an error