How to select clear table contents without destroying the table

Question

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

User · Answer

There is a condition that most of these solutions do not address. I revised Patrick Honorez's solution to handle it. I felt I had to share this because I was pulling my hair out when the original function was occasionally clearing more data that I expected.

The situation happens when the table only has one column and the .SpecialCells(xlCellTypeConstants).ClearContents attempts to clear the contents of the top row. In this situation, only one cell is selected (the top row of the table that only has one column) and the SpecialCells command applies to the entire sheet instead of the selected range. What was happening to me was other cells on the sheet that were outside of my table were also getting cleared.

I did some digging and found this advice from Mathieu Guindon: Range SpecialCells ClearContents clears whole sheet

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.

If the list/table only has one column (in row 1), this revision will check to see if the cell has a formula and if not, it will only clear the contents of that one 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

A final step I included is a tip that is attributed to John Walkenbach, sometimes noted as J-Walkenbach tip 73 Automatically Resetting The Last Cell

User · Answer

I use this code to remove my data but leave the formulas in the top row  It also removes all rows except for the top row and scrolls the page up to the top    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

User · Answer

Try just clearing the data  not the entire table including headers    ACell ListObject DataBodyRange ClearContents

User · Answer

I reworked Doug Glancy s solution to avoid rows deletion  which can lead to  Ref issue in formulae     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

User · Answer

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

[excel] How to select clear table contents without destroying the table?

Examples related to excel

Examples related to excel-2010

Examples related to vba