dpaton05
Well-known Member
- Joined
- Aug 14, 2018
- Messages
- 2,352
- Office Version
- 365
- 2016
- Platform
- Windows
I have a table with a delete all rows button that is meant to delete all the rows so more rows can be added. In the past, it has deleted all the rows but left one blank row and this allowed me to add more rows but now when I press it, it deletes even the last row, leaving just the header.
How do I make it so there is still one row left, but it is blank when I press the button?
Here is the delete lines code:
Thanks
How do I make it so there is still one row left, but it is blank when I press the button?
Here is the delete lines code:
VBA Code:
Sub CostingDeleteAll()
'Deleting The Data In A Table
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
End Sub
Thanks