VBA to clear table not working properly

[gheyman]

This code works fine when my table has three fields (coulumns). But if I try to use it on a table with one column it does not. The entire table disappears.

How do I change this to work on a table that only has one field?

Sub ClearUserList()

'Clear table - remove all rows except the first row
    Application.ScreenUpdating = False

    ActiveSheet.ListObjects("UserDefinedList").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("User List").ListObjects("UserDefinedList")
    .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

    Sheet1.Range("A21").Activate
    
End Sub

thanks for the help!

 

[RoryA]

If you apply SpecialCells to one cell (which is what you would be doing if your table only has one column), it will actually apply to the entire sheet. You could use something like:

If .Listcolumns.count > 1 then
.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents
else
with .databodyrange.cells(1)
   if not .hasformula then .clearcontents
end with
end if

 

[gheyman]

To clarify: How do I use this on a table that has only one column?

 

[RoryA]

Replace the line .DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents in your code with what I posted.

 

[gheyman]

Thank you very much. That worked!