Excel Listobject table ListRows.count vs range.rows.count

Question

I am working with listobjects in Excel and I have the following problem: I add data to a table everytime a code is run. Previously I have to delete all the old data.

ThisWorkbook.Sheets("comm").ListObjects(1).DataBodyRange.Delete

What happend afterwards is that I get an error with:

myNrofRowsinCOMM = COMMtbl.DataBodyRange.Rows.Count

I had a look to this post at no avail. I still dont understand what is going on.

I tried the follwoing as well:

MsgBox "COMMtbl.Range.Rows.Count:" & COMMtbl.Range.Rows.Count
MsgBox "COMMtbl.ListRows.Count:" & COMMtbl.ListRows.Count
MsgBox "COMMtbl.databodyRange.Rows.Count:" & COMMtbl.DataBodyRange.Rows.Count
If COMMtbl.Range.Rows.Count = 1 Then
    COMMtbl.ListRows.Add (1)
End If

If the table is empty (row headers and an empty first row) the first line gives 2. So the range has 2 rows which seems according to reality. COMMtbl.Range.Rows.Count=2 the sencond one gives 0. Which I dont understand at all. COMMtbl.ListRows.Count=0 And the third one gives an error "Object variable or withblcok variable not set"

I am trying to add rows to the table and fill them with data, for that I add a row and populate it. I want to add a row at the end, therefore I need everytime to count the number of rows. ALL fine except for the firts one when I previously deleted the whole content of the table which looks like:

Any help is welcome

Thanks a lot.

Range, ListRows and DataBodyRange are different. ListRows and DataBodyRange are of the type Range but there coverages are different. DataBodyRange = range that contains the data area in the list between the header row and the insert row. If you have only header and insert row , it returns null, hence the error. — cyboashu
– cyboashu, Commented Apr 3, 2018 at 9:05

paul bica · Accepted Answer · 2018-04-03 21:15:09Z

5

I needed a refresher, so this might help you too:

.

.Range - Includes the Header, Insert Row and Totals Row (if visible)


.DataBodyRange
  - Contains the data area, between the Header Row and the Insert Row
  - If the ListObject doesn't have a DataBodyRange, this property returns Null


.ListRows
  - Represents all the rows of data (doesn't include Header, Total, or Insert rows)
  - To delete any items from this collection, do not use the Delete method of the item
    Use the Delete method of the range of the item to delete the item
    For example ListRows.Item(1).Range.Delete()

.

When you do DataBodyRange.Delete the table doesn’t have the DataBodyRange object anymore, so to confirm that there are no rows with data in the table, replace

myNrofRowsinCOMM = COMMtbl.DataBodyRange.Rows.Count

with

myNrofRowsinCOMM = COMMtbl.ListRows.Count

More details from MSDN - ListObject

edited Apr 3, 2018 at 21:15

answered Apr 3, 2018 at 20:30

paul bica

10.8k4 gold badges27 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

LFB · Accepted Answer · 2018-04-04 03:34:37Z

If you don't have data in the ListObject.DataBodyRange Is Nothing, so you can't count rows. You can get the last row of the ListObject by using n = ListObject.Range.Rows.Count and then ListObject.ListRows(n).Range

I don't know how the data you have at hand looks like, but for the sake of the example, if all you had was one column and one row, you could add the data to the last row without worrying if the table is empty or not and then using the .Add method in your example.

Dim current_n_rows As Integer

With ThisWorkbook.Sheets("comm").ListObjects(1)
    .DataBodyRange.Delete
    current_n_rows = .ListRows.Count
    .ListRows(current_n_rows).Range.Value = NewData
    .ListRows.Add
End With

You can wrap this statement around the loops you would need to fill the table.

Hope it helps! Cheers!

Collectives™ on Stack Overflow

Excel Listobject table ListRows.count vs range.rows.count

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related