Selecting Table on Sheet

[Tripp]

Hello,

I am tying to clear the contents of a table so that when I paste in new values the data will still have the same table name. What I have so far requires me to have clicked on the table so that 'Activecell' Works. I would like it so that the vba selects the table without the user having to.

Sub ClearTable()

Application.ScreenUpdating = False


'Clears the table contents

If Not ActiveCell.ListObject Is Nothing Then
ActiveCell.ListObject.DataBodyRange.Rows.ClearContents
End If

'Shrinks the Table  to one blank row

If Not ActiveCell.ListObject Is Nothing Then
    ActiveCell.ListObject.DataBodyRange.Delete
End If

Application.ScreenUpdating = True

End Sub

Thanks
Tripp

 

[NARAYANK991]

Hi ,

See if this works :

Sub ClearTable()
    Dim lo As ListObject
    Application.ScreenUpdating = False

    Set lo = ActiveSheet.ListObjects("Table1")  ' Change the name of the table to suit

    If Not lo Is Nothing Then
      lo.DataBodyRange.Delete
    End If
   
    Application.ScreenUpdating = True
End Sub

Narayan

 

[Tripp]

Hi Narayan,

I've pasted your code in but it says the sub/ function is not defined and highlights the macro title in the code.. Not sure how to amend.

 

[NARAYANK991]

Hi ,

Where have you pasted this code ? In a Worksheet section , a code module or in the ThisWorkbook section ?

Paste it in the worksheet section corresponding to the worksheet where you have your table. If you paste it in a code module , ensure that the worksheet where you have your table is activated before you run the macro.

Narayan

 

[Tripp]

I have pasted the code into the Worksheet with the table and get the same response. I am now getting this error when I paste the original code back to the same place...?

 

[NARAYANK991]

Hi ,

Is ClearTable a keyword ? Can you try by renaming the macro to temp ?

Narayan

 

[Tripp]

I found that it only works if I re-type the whole code out... not sure whats going on but the workaround isn't too much trouble.