Delete only table data using VBA

craig2525

New Member
Joined
Oct 30, 2018
Messages
44
Office Version
  1. 2016
Platform
  1. Windows
Hi all. I have a sheet that I need to clear the contents of a table but not the entire sheet. The table is in columns B through U. I have other data in the sheet that I do not want to delete. They are in columns AA through AN. I have tried protecting the cells but I get and error when doing this. Here is the code I am using.

Excel 5.PNG
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You have a filter on. Are you only trying to delete visible rows ?
If it is all rows all you need to do is to take the filter off.
VBA Code:
Sub clearTable
    With toSched.ListObjects("ToScheduleTbl")
        If Not .DataBodyRange Is Nothing Then
            .Range.AutoFilter
            .DataBodyRange.Delete
        End If
     End With  
End Sub

If you only want to delete visible rows, it will need a different approach.
 
Upvote 0
Just the data in the table. I want to keep the formulas in the table and any data outside the table.
 
Upvote 0
Just the data in the table. I want to keep the formulas in the table and any data outside the table.
If you are happy to delete all the data and keep the formulas then the only reason your code is not working is that the delete table rows (ListRows) because the table has a filter applied.
The below code removed the filter before clearing the data.
It may look like the formulas are gone but if you enter something into any (non-formula) field in the row, you should find that the formula are there.

VBA Code:
Sub clearTable()

    With toSched.ListObjects("ToScheduleTbl")
        If Not .DataBodyRange Is Nothing Then
            .ShowAutoFilter = True
            If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
            .DataBodyRange.Delete
        End If
     End With
     
End Sub
 
Upvote 0
Solution
This will delete all listrows from the table and leave the formulas in place,
but I have no idea as to what is being written to the table after this is run.
VBA Code:
Sub clearTable()

Application.ScreenUpdating = False

    With toSched.ListObjects("ToScheduleTbl")
        If Not .DataBodyRange Is Nothing Then
            .ShowAutoFilter = True
            If .AutoFilter.FilterMode Then .AutoFilter.ShowAllData
            If Not .DataBodyRange Is Nothing Then
                For i = .ListRows.Count To 1 Step -1
                    .ListRows(i).Delete
                Next i
            End If
        End If
    End With
          
Application.ScreenUpdating = True

End Sub
 
Upvote 0
Thank you very much. The first sub-routine worked perfectly for me. Now to let my team work with it and get final approval. Lol. Thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,534
Messages
6,114,188
Members
448,554
Latest member
Gleisner2

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
Back
Top