Excel Table: find sum of databodyrange

I have a table with varying columns and rows. I need to calculate the sum of the entire databodyrange. I tried the following without success:

Sub tblSum()
    Dim chkEmpty As Long
    Dim ws As Worksheet
    Dim lo As ListObject
            
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set lo = ws1.ListObjects("tblSummary")

    chkEmpty = WorksheetFunction.Sum(lo1.DataBodyRange.value)
End Sub

Suggestions greatly appreciated!

tom

Sub tblSum()
    Dim chkEmpty As Long
    Dim ws As Worksheet
    Dim lo As ListObject
            
    Set ws = ThisWorkbook.Worksheets("Summary")
    Set lo = ws.ListObjects("tblSummary")

    chkEmpty = WorksheetFunction.Sum(lo.DataBodyRange.value)
End Sub

You probably need to specify the column no as well, unless all columns are to be added. Like

chkEmpty = WorksheetFunction.Sum(lo.ListColumns(4).DataBodyRange)

Thanks AB33,

I suppose I could use ListColumns but how would I list all of the columns that way?

If it all columns why do not you use bodyrange then?

CountCol = lo.ListColumns.Count ' gives the columns count.

No tested
chkEmpty = WorksheetFunction.Sum(lo.ListColumns.DataBodyRange)

That's the problem. I WANTED to use DataBodyRange but if you will look at the code, it does not work so that is what I need help with fixing.

I have now tested it and it works on my sample.

chkEmpty = WorksheetFunction.Sum(lo.DataBodyRange)

My sample has column A as string address and the remaining columns are numbers.

here is the line that is not working:

chkEmpty = WorksheetFunction.Sum(lo1.DataBodyRange.value)

The error I get on my production file is: Application-define or object-defined error.

any thoughts?

Hey AB33,

As Pogo once said, "We have found the enemy and he is us."

The problem was with my original production table. User error.

Thanks for your effort!

tom

Strange. It worked for me on a simple test file too.... gotta check what is different about my production file.

In post 2, I highlighted with red the errors on your code. Table names are so weird, so you need to double check the names as well.

chkEmpty = WorksheetFunction.Sum(lo.DataBodyRange.value)

Not

chkEmpty = WorksheetFunction.Sum(lo1.DataBodyRange.value)