0 votes
in VBA by Expert (848 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 (2.9k 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 (848 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 (2.9k points)
This is a great tip! I'm really glad you shared it!

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

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

...