How to select clear table contents without destroying the table?

excel, excel-2010, vba

Solution

How about:

ACell.ListObject.DataBodyRange.Rows.Delete

That will keep your table structure and headings, but clear all the data and rows.

EDIT: I'm going to just modify a section of my answer from your previous post, as it does mostly what you want. This leaves just one row:

With loSource
   .Range.AutoFilter
   .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete
   .DataBodyRange.Rows(1).Specialcells(xlCellTypeConstants).ClearContents
End With

If you want to leave all the rows intact with their formulas and whatnot, just do:

With loSource
   .Range.AutoFilter
   .DataBodyRange.Specialcells(xlCellTypeConstants).ClearContents
End With

Which is close to what @Readify suggested, except it won't clear formulas.

Problem

I have a vba function in excel 2010 that I built using help from people on here. This function copies the contents of a table/form, sorts them, and sends them to the appropriate tables. Now after running this function I want the original table to be cleared. I can achieve this with the following code, assuming ACell has been defined as the first cell in the table. `ACell.ListObject.Range.ClearContents` works fine, the only problem is it deletes the table as well as the data values. Is there any way around this? I would rather not have to set the table up every time I enter some data.

Original source

Related problems