How to select clear table contents without breaking the table?

Question

I have a vba function in excel 2010 that I created with help from people here. This function copies the contents of the table / form, sorts them, and sends them to the corresponding 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 working fine, the only problem is deleting the table as well as the data values.

Is there any way around this? I would prefer not to set the table every time I enter some data.

Answer 1

What about:

 ACell.ListObject.DataBodyRange.Rows.Delete 

This will preserve the table structure and headers, but will clear all data and rows.

EDIT: I'm going to just change the section of my answer from your previous post , as it does basically what you want. This leaves only 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 lines unchanged with your formulas and so on, just do:

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

Which is close to what @Readify suggested, except that it does not clear formulas.

Answer 2

Try to just clear the data (not the whole table, including the headers):

 ACell.ListObject.DataBodyRange.ClearContents 

Answer 3

I reworked Doug Glancey's solution to avoid deleting lines, which could lead to #Ref in formulas problem.

 Sub ListReset(lst As ListObject) 'clears a listObject while leaving row 1 empty, with formulae With lst If .ShowAutoFilter Then .AutoFilter.ShowAllData On Error Resume Next With .DataBodyRange .Offset(1).Rows.Clear .Rows(1).SpecialCells(xlCellTypeConstants).ClearContents End With On Error GoTo 0 .Resize .Range.Rows("1:2") End With End Sub 

Answer 4

I use this code to delete my data, but leave the formulas on the top line. It also deletes all lines except the top line, and scrolls the page up.

 Sub CleanTheTable() Application.ScreenUpdating = False Sheets("Data").Select ActiveSheet.ListObjects("TestTable").HeaderRowRange.Select 'Remove the filters if one exists. If ActiveSheet.FilterMode Then Selection.AutoFilter End If 'Clear all lines but the first one in the table leaving formulas for the next go round. With Worksheets("Data").ListObjects("TestTable") .Range.AutoFilter On Error Resume Next .DataBodyRange.Offset(1).Resize(.DataBodyRange.Rows.Count - 1, .DataBodyRange.Columns.Count).Rows.Delete .DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents ActiveWindow.SmallScroll Down:=-10000 End With Application.ScreenUpdating = True End Sub 

Answer 5

There is a condition that most of these decisions are not considered. I reviewed Patrick Honorez's decision to handle this. I felt I had to share this because I was tearing my hair out when the original function sometimes deleted more data that I expected.

The situation occurs when the table has only one column and .SpecialCells(xlCellTypeConstants).ClearContents tries to clear the contents of the top row. In this situation, only one cell is selected (the top row of the table, in which there is only one column), and the SpecialCells command is applied to the entire sheet, and not to the selected range. What happened to me was that other cells on the sheet that were outside of my desk were also cleared.

I rummaged around a bit and found this tip from Mathieu Gindon: Range SpecialCells ClearContents clears the entire sheet

Range ({any single cell}). SpecialCells ({whatever}) seems to work on the whole sheet.

Range ({more than one cell}). SpecialCells ({whatever}) seems to work from the specified cells.

If the list / table has only one column (in row 1), this edition will check if the cell has a formula, and if not, it will clear only the contents of this single cell.

 Public Sub ClearList(lst As ListObject) 'Clears a listObject while leaving 1 empty row + formula ' https://stackoverflow.com/a/53856079/1898524 ' 'With special help from this post to handle a single column table. ' Range({any single cell}).SpecialCells({whatever}) seems to work off the entire sheet. ' Range({more than one cell}).SpecialCells({whatever}) seems to work off the specified cells. ' https://stackoverflow.com/questions/40537537/range-specialcells-clearcontents-clears-whole-sheet-instead On Error Resume Next With lst '.Range.Worksheet.Activate ' Enable this if you are debugging If .ShowAutoFilter Then .AutoFilter.ShowAllData If .DataBodyRange.Rows.Count = 1 Then Exit Sub ' Table is already clear .DataBodyRange.Offset(1).Rows.Clear If .DataBodyRange.Columns.Count > 1 Then ' Check to see if SpecialCells is going to evaluate just one cell. .DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents ElseIf Not .Range.HasFormula Then ' Only one cell in range and it does not contain a formula. .DataBodyRange.Rows(1).ClearContents End If .Resize .Range.Rows("1:2") .HeaderRowRange.Offset(1).Select ' Reset used range on the sheet Dim X X = .Range.Worksheet.UsedRange.Rows.Count 'see J-Walkenbach tip 73 End With End Sub 

The last step I included is the advice that is attributed to John Walkenbach, sometimes referred to as the J-Walkenbach tip 73 Automatic J-Walkenbach tip 73 last cell

Answer 6

This code is great. I have used this many times, as that was exactly what I was looking for. I have a question - What if I want this macro to clear more than one table? I have another tab that I would like to clear at the same time.

I am very new to VBA and it is hard for me to figure out where to add code to clear the second table.