Delete all rows in a table

dpaton05

Well-known Member
Joined
Aug 14, 2018
Messages
1,714
I want to delete all the rows in an excel table except the first and clear the contents, but not delete the formulas in the row. Can someone help me with the excel code.

The sheet is called home and the table is called tblCosting.

Thanks,
Dave
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

ZVI

MrExcel MVP
Joined
Apr 9, 2008
Messages
3,722
Office Version
  1. 2016
  2. 2010
  3. 2007
Platform
  1. Windows
Hi.
Try this:
Rich (BB code):
Sub ClearTable()
  Worksheets("Home").ListObjects("tblCosting").DataBodyRange.Delete
End Sub
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
Try:

Code:
Sub ResetTable()
    'Deleting The Data In A Table
    Dim tbl As ListObject
    Dim cell As Range
    
    Set tbl = Sheets("home").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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,954
Messages
5,525,880
Members
409,669
Latest member
JDCupps

This Week's Hot Topics

Top