Clear Table Code not working

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,847
Office Version
  1. 365
Platform
  1. Windows
I dont know what I changed, but this code use to work. Now it deletes everything including the header. The complete table is removed

it use to only clear all the rows with the exception of the header

Code:
Sub Clear_ItemID()

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

    ActiveSheet.ListObjects("TableItemID").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("ItemId").ListObjects("TableItemID")
            .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("B21").Activate

End Sub

thanks for the help
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows
You're code work for me, but try
VBA Code:
Sub gheyman()
   With Sheets("ItemId").ListObjects("TableItemID")
      If .ShowAutoFilter Then
         If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
      End If
      With .DataBodyRange
         .Offset(1).Resize(.Rows.Count - 1).Delete
         .SpecialCells(xlConstants).ClearContents
      End With
   End With
End Sub
 
Solution

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,847
Office Version
  1. 365
Platform
  1. Windows
Thank you! Your code worked.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,251
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback
 

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,847
Office Version
  1. 365
Platform
  1. Windows
I figured out why my original code was giving me problems. If the table was blank (didn't have more then one row) the code doesn't work.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,973
Messages
5,656,179
Members
418,287
Latest member
reba557

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top