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.

score 47 · Accepted Answer · edited Jul 20 '18 at 16:55

47

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.

edited Jul 20 '18 at 16:55

FreeMan

5,660
1
27
53

answered Apr 19 '12 at 03:05

Doug Glancy

27,214
6
67
115

1

So if i delete all the rows what happens? Will it leave 1 blank one or will i just have named table with no cells, i have no header of footer on this table. – SpeedCrazy Apr 19 '12 at 03:31
Just tried it and this totally deletes the table, formulas, data validation, formatting, everything! – SpeedCrazy Apr 19 '12 at 03:33
1

Okay, take a deep breath :). It would help if you mentioned things like data validation and formulas in your question. The previous question I answered on this topic included the code to leave one row. Is that what you want? – Doug Glancy Apr 19 '12 at 03:41
Well i still have to clear that row... but yes. i had just assumed that so long as the table was left intact, and i did detail that in my question, that the formulas and stuff that was automatically in each row would also be preserved. – SpeedCrazy Apr 19 '12 at 03:43
You did say "left intact," but that could mean different things. Anyways, give me a minute and I'll edit my answer. – Doug Glancy Apr 19 '12 at 03:44
That works, minor gripe, well not gripe, is there anyway to remove the header? The titles above my columns are set by formulas so i try to turn headers off as they don't support formulas. No biggie if they cant be turned off automatically. Thanks a million doug. – SpeedCrazy Apr 19 '12 at 04:06
You're welcome! I don't think you can have a table without headers, or blank headers, which makes sense as the column names act as field names. You can change the names in VBA, and even refer to another cell, like: ACell.ListObject.ListColumns(1).Name = Activesheet.Range("E1").Value – Doug Glancy Apr 19 '12 at 04:15
6

+ 1 for patiently handling this question :) – Siddharth Rout Apr 19 '12 at 10:50
https://msdn.microsoft.com/EN-US/library/office/ff196678.aspx gives a similar offset/resize example @DougGlancy gives above – northern-bradley Feb 07 '16 at 16:11

score 10 · Answer 2 · answered Apr 19 '12 at 03:12

10

Try just clearing the data (not the entire table including headers):

ACell.ListObject.DataBodyRange.ClearContents

answered Apr 19 '12 at 03:12

Reafidy

8,240
5
51
83

If you want to leave the table rows and just clear the contents then use the above. If you want to delete the rows as well then use Doug's answer. – Reafidy Apr 19 '12 at 03:16
1

+1 for seeing this with fresh eyes and giving the closest answer on the info at the time. – Doug Glancy Apr 19 '12 at 13:52

iDevlop · Answer 3 · 2019-05-24T07:57:31.097

4

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

edited May 24 '19 at 07:57

answered Dec 19 '18 at 17:07

iDevlop

24,841
11
90
149

I liked this solution the best but noticed that if the list/table only has one column, the `.ClearContents` would clear other cells on the sheet outside of the list. I posted a revised version [here](https://stackoverflow.com/a/56624448/1898524) – Ben Jun 17 '19 at 03:52
@Ben You're perfectly right, I also had that issue :/ – iDevlop Jun 17 '19 at 08:51

score 3 · Answer 4 · edited Jun 20 '20 at 09:12

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

score 1 · Answer 5 · answered Feb 04 '16 at 16:44

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

score 1 · Answer 6 · answered Jun 11 '21 at 10:34

I usually use something very simple if you just want to clear table contents.

Sub Clear_table()
Range("Table1").ClearContents
End Sub

Obviously if you have a workbook with multiple pages you might want to change the code to accommodate that.

Sub Clear_table()
Worksheets("Sheet1").Range("Table1").ClearContents
End Sub

score 0 · Answer 7 · edited Jun 17 '21 at 06:34

0

If you want to delete the entire table except your headers, and your formula, you can try this:

Sub DeteteTableExceptFormula()
    Dim tb As ListObject
    Set tb = activeworksheet.ListObjects("MyTable")
    tb.DataBodyRange.Delete
End Sub

edited Jun 17 '21 at 06:34

Vega

27,856
27
95
103

answered Jun 16 '21 at 09:20

Marks

1

How to select clear table contents without destroying the table?

7 Answers7

Linked