Delete contents of a row but don't clear if the cell contains a formula

[dpaton05]

I have a procedure that runs to delete all contents of an excel table. The sub is meant to delete all the rows except the first. When it gets to the first row, it needs to clear each cell if the cell contains no formula. It did work so I am not sure why it has stopped. This is my code:

Sub CostingDeleteAll()
    Costing.Unprotect Password:=ToUnlock
        Dim tbl As ListObject
        Dim cell As Range
            Set tbl = ThisWorkbook.Worksheets("Costing_tool").ListObjects("tblCosting")
            'Delete all table rows except first row
            With tbl.DataBodyRange
                If .Rows.Count > 1 Then
                    .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
                End If
                'Clear the contents, but not delete the formulas
                For Each cell In tbl.ListRows(1).Range.Cells
                    If Not cell.HasFormula Then
                        cell.Value = ""
                    End If
                Next
            End With
    Costing.Protect Password:=ToUnlock
End Sub

When the above procedure runs, it gives an error message, "Application defined or object defined error" and if I press debug, the following line of code is highlighted:

cell.Value = ""

Could someone help me please as I do not know a great deal about VBA?

 

[dpaton05]

I worked it out with this code.

Sub CostingDeleteAll()
    Costing.Unprotect Password:=ToUnlock
        Dim tbl As ListObject
        Dim cell As Range
            Set tbl = ThisWorkbook.Worksheets("Costing_tool").ListObjects("tblCosting")
            'Delete all table rows except first row
            With tbl.DataBodyRange
                If .Rows.Count > 1 Then
                    .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
                End If
                'Clear the contents, but not delete the formulas
                Dim rng As Range
                Set rng = Costing.Range("A5:L5")
                    rng.Cells.SpecialCells(xlCellTypeConstants).Clear
                
               ' For Each cell In tbl.ListRows(1).Range.Cells
                '    If Not cell.HasFormula Then
                 '       cell.Value = ""
                  '  End If
                'Next
            End With
    Costing.Protect Password:=ToUnlock
End Sub

 

[dpaton05]

Actually, it only partially works. I get an error if no cells are found. How do I get rid of the error?

 

[dpaton05]

I worked it out with this code:

Sub CostingDeleteAll()
    On Error GoTo Errhandler
    Costing.Unprotect Password:=ToUnlock
        Dim tbl As ListObject
       ' Dim cell As Range
            Set tbl = ThisWorkbook.Worksheets("Costing_tool").ListObjects("tblCosting")
            'Delete all table rows except first row
            With tbl.DataBodyRange
                If .Rows.Count > 1 Then
                    .Offset(1, 0).Resize(.Rows.Count - 1, .Columns.Count).Rows.Delete
                End If
                'Clear the contents, but not delete the formulas
                Dim rng As Range
                Set rng = Costing.Range("A5:L5")
                    rng.Cells.SpecialCells(xlCellTypeConstants).Clear
                
               ' For Each cell In tbl.ListRows(1).Range.Cells
                '    If Not cell.HasFormula Then
                 '       cell.Value = ""
                  '  End If
                'Next
            End With
    Costing.Protect Password:=ToUnlock
Errhandler: If Err.Number = 1004 Then Exit Sub
End Sub

 

[MARK858]

@dpaton05 when your code errors does your sheet not stay unprotected?