0 votes
in VBA by Expert (916 points)

Hello everyone

I am using the following code to delete the databody range of the table so as to put new data

Sub RemoveTableBodyData()

Dim tbl As ListObject

Set tbl = ActiveSheet.ListObjects(1)

'Delete Table's Body Data
  If tbl.ListRows.Count >= 1 Then
    tbl.DataBodyRange.Delete
  End If
  
End Sub

I execute the previous code then there is a nother macro that I need to pouplate the array data into the table

Sub NewData()
Dim rng As Range, a
    Set rng = Worksheets(2).ListObjects(1).DataBodyRange
    Set rng = Intersect(rng, rng.Offset(0, 1))
    a = rng.Value
    
    Worksheets(8).ListObjects(1).DataBodyRange.Range("B1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

But I got an error 91 (Object Variable or With block not set)

Thanks advanced for help

2 Answers

+1 vote
by Super Expert (3.2k points)
selected by
 
Best answer

Since the table is empty, the DataBodyRange property doesn't exist. To fix this, you need to add a line to your table using .ListRows.Add. Your new macro will look like this:

Sub NewData()
Dim rng As Range, a
    Set rng = Worksheets(2).ListObjects(1).DataBodyRange
    Set rng = Intersect(rng, rng.Offset(0, 1))
    a = rng.Value
    Worksheets(8).ListObjects(1).ListRows.Add
    Worksheets(8).ListObjects(1).DataBodyRange.Range("B1").Resize(UBound(a, 1), UBound(a, 2)).Value = a
End Sub

 

by Expert (916 points)
That's wonderful. Thank you very much.

By the way how can I determine the last row in table ..? as when using the normal approach I got the last row in table but I need the last row with the non-empty data.
+1 vote
by Beginner (16 points)

Ryan, I love your site. I use this one liner to clear table databody ranges after I have named the table:
 

If [tblName].Rows.Count > 1 Then [tblName].Delete Else [tblName].ClearContents

 

by Super Expert (3.2k points)
This is a great tip! I'm really glad you shared it!

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...