Delete all data rows from an Excel table (apart from the first)

excel vba excel-2007
David Gard picture David Gard · Dec 18, 2013 · Viewed 115.8k times · Source

Just recently I've been trying to delete all data rows in a table, apart from the first (which needs to just be cleared)

Some of the tables being actioned could already have no rows, so I was running it to problems as using .DataBodyRange.Rows.Count on a table with no rows (just header and/or footer) causes errors.

I looked all over for a solution an could not find a whole one, so I hope my answer to this question will be useful to others in the future.

Answer

royUK picture royUK · Jul 2, 2016

This is how I clear the data:

Sub Macro3()
    With Sheet1.ListObjects("Table1")
        If Not .DataBodyRange Is Nothing Then
            .DataBodyRange.Delete
        End If
    End With
End Sub

Related questions

How to do a "Save As" in vba code, saving my current Excel workbook with datestamp?

I have an Excel Workbook that on form button click I want to save a copy of the workbook with the filename being the current date. I keep trying the the following ActiveWorkbook.SaveAs ("\\filePath\FormFlow To MSExcel\" & Left(…

Read More
Closing Excel Application using VBA

I have used the following without success. The active workbook closes, indeed, but the excel window remains open. Application.ActiveWindow.Close SaveChanges:=False ActiveWorkbook.Close SaveChanges:=False Which is the command that terminates the application? EDIT To say a little …

Read More
Excel Date Conversion from yyyymmdd to mm/dd/yyyy

I have been searching for about an hour on how to do this in Excel. I have an Excel file that was created from an old system and I am pulling information from a SQL Server Database, I will be …

Read More