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

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,157
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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:

VBA 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:
VBA Code:
cell.Value = ""

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

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,157
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I worked it out with this code.

VBA 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

Well-known Member
Joined
Aug 14, 2018
Messages
2,157
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Actually, it only partially works. I get an error if no cells are found. How do I get rid of the error?
 

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
2,157
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I worked it out with this code:

VBA 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
 
Solution

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
14,007
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@dpaton05 when your code errors does your sheet not stay unprotected?
 

Watch MrExcel Video

Forum statistics

Threads
1,128,154
Messages
5,629,005
Members
416,358
Latest member
grsaltzman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top