Clear a Table

gheyman

Well-known Member
Joined
Nov 14, 2005
Messages
1,835
Office Version
  1. 365
Platform
  1. Windows
I have code in one workbook that clears a table. It works great. performs the code in seconds. I tried to copy the code to another workbook to perform the same process but in the new workbook it takes almost 10 minutes to run. Not sure what's the difference and why it would take so long in the second workbook. (even if I put the same number of rows in both tables)

The first code is where it works in seconds

Code:
Sub Clear_ItemID()

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

    ActiveSheet.ListObjects("ItemIDTable").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("ItemIdList").ListObjects("ItemIDTable")
    .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

code that takes forever to run

Code:
Sub ClearTable()
'Clear table - remove all rows except the first row
    Application.ScreenUpdating = False

    ActiveSheet.ListObjects("TableSQDSearch").HeaderRowRange.Select
    
    'Remove the filters if one exists.
    
    If ActiveSheet.AutoFilterMode Or ActiveSheet.FilterMode Then
        ActiveSheet.ShowAllData
    End If
    
    
    
    'Clear all lines but the first one in the table leaving formulas for the next go round.
    With Worksheets("Search").ListObjects("TableSQDSearch")
    .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
    
  '  Sheet2.ShowAllData
    
    Application.ScreenUpdating = True

    Range("B26").Activate
End Sub

Thanks
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,409
Office Version
  1. 2013
Platform
  1. Windows
If you want to clear all the value in the Table why not used something like this:
VBA Code:
Sub Clear_Table_Range()
'Modified 12/15/2020  3:29:59 PM  EST
Application.ScreenUpdating = False
ActiveSheet.ListObjects("Table5").DataBodyRange.ClearContents
End Sub
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,409
Office Version
  1. 2013
Platform
  1. Windows
Now if you actually wanted to delete all the rows in the Table except for the fist row.
Try this:
VBA Code:
Sub Clear_Table_Range()
'Modified 12/15/2020  3:43:59 PM  EST
Application.ScreenUpdating = False
Dim ans As Long
ans = ActiveSheet.ListObjects("Table5").DataBodyRange.Rows.Count
ActiveSheet.ListObjects("Table5").DataBodyRange.Rows(1).Resize(ans - 1).Delete
Application.ScreenUpdating = True
End Sub
 
Solution

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,409
Office Version
  1. 2013
Platform
  1. Windows
Now if you actually wanted to delete all the rows in the Table except for the fist row.
Try this:
VBA Code:
Sub Clear_Table_Range()
'Modified 12/15/2020  3:43:59 PM  EST
Application.ScreenUpdating = False
Dim ans As Long
ans = ActiveSheet.ListObjects("Table5").DataBodyRange.Rows.Count
ActiveSheet.ListObjects("Table5").DataBodyRange.Rows(1).Resize(ans - 1).Delete
Application.ScreenUpdating = True
End Sub
I always like it when posters say thanks that worked. I don't like this new feature where all poster does is click on solution button.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,316
Messages
5,623,961
Members
416,002
Latest member
Neshx

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