How to suppress an error message

[dpaton05]

I have the following code that deletes rows in a table. When I get down to nothing in the last row and press the button again, I get an error message saying there are no cells found. How do I suppress that message so nothing will happen?

Private Sub cmdDeleteRow_Click()

'ActiveSheet.Unprotect Password:="npssadmin"

   Dim ans As Long
    With ActiveSheet.ListObjects("tblCosting").DataBodyRange
        ans = .Rows.Count
        If ans > 1 Then .Rows(ans).Delete
        If ans = 1 Then .Rows(1).Cells.SpecialCells(xlCellTypeConstants).ClearContents
    End With

    'Selection.ListObject.ListRows(6).Delete
    
'ActiveSheet.Protect Password:="npssadmin"
Application.EnableEvents = True
End Sub

 

[Michael M]

Try using

Private Sub cmdDeleteRow_Click()
'ActiveSheet.Unprotect Password:="npssadmin"
   Dim ans As Long
    With ActiveSheet.ListObjects("tblCosting").DataBodyRange
        ans = .Rows.Count
        If ans=0 then exit sub
        If ans > 1 Then .Rows(ans).Delete
        If ans = 1 Then .Rows(1).Cells.SpecialCells(xlCellTypeConstants).ClearContents
    End With
    'Selection.ListObject.ListRows(6).Delete
'ActiveSheet.Protect Password:="npssadmin"
Application.EnableEvents = True
End Sub

 

[dpaton05]

Try using

Private Sub cmdDeleteRow_Click()
'ActiveSheet.Unprotect Password:="npssadmin"
   Dim ans As Long
    With ActiveSheet.ListObjects("tblCosting").DataBodyRange
        ans = .Rows.Count
        If ans=0 then exit sub
        If ans > 1 Then .Rows(ans).Delete
        If ans = 1 Then .Rows(1).Cells.SpecialCells(xlCellTypeConstants).ClearContents
    End With
    'Selection.ListObject.ListRows(6).Delete
'ActiveSheet.Protect Password:="npssadmin"
Application.EnableEvents = True
End Sub

Still says no cells found and highlights this code:

.Rows(1).Cells.SpecialCells(xlCellTypeConstants).ClearContents

 

[Michael M]

shouldn't this be

.Rows(1).Cells.SpecialCells(xlCellTypeConstants).ClearContents

.Rows([color=red]ans[/color]).Cells.SpecialCells(xlCellTypeConstants).ClearContents