gheyman
Well-known Member
- Joined
- Nov 14, 2005
- Messages
- 2,341
- Office Version
- 365
- Platform
- 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
thanks for the help
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