Find the last cell column in table with Total Row

user_3563995 0
Viewed 142

I have a table that contains header row, data body and totals row. I want to find the last row in the last column (in this case) of the data body and I want to have the value of the totals row.

My Table with header row, data body and totals row

I can get the last row and column value (135) by this VBA

Sub Target()

Dim ws1 As Worksheet
Set ws1 = Worksheets("Nordics")
Dim ams As Long
Dim Target As Variant
ans = ws1.ListObjects("TableNordics").DataBodyRange.Rows.Count

Target = ws1.ListObjects("TableNordics").DataBodyRange(ans, 8).Value

End Sub

But, I can not get something simmilar to work for the Totals row (note, message box is just to see if the value works

Sub Table_Lastrow()

Dim ams As Long
ans = ActiveSheet.ListObjects("TableNordics").TotalsRowRange.Value
MsgBox "Last row in Table is  " & ans
End Sub

When I get this to work, I would like to change the Worksheet and Table name to be variable, eg. I will have the names in Cell A1 And A2, I have tried a couple of things for the first formula, but I can not get it to work.

excel-formula vba excel
2 Answers

You just need to refer to the cell in the last column, which can be done using .ListColumns.Count:

Dim tbl As ListObject
Set tbl = ws1.ListObjects("TableNordics")

With tbl
    ans = .TotalsRowRange.Cells(.ListColumns.Count).Value
End With
user_92458530

You can access the Total row values using the ListObject.TotalsRowRange property. You need to ensure that the ListObject.ShowTotals = True to ensure to avoid a possible Object variable or With block variable not set error.

Personally I would use the WorksheetFunction.Subtotal (see TargetAverage()) to return the value. In this way, you would not have to upate your code if the TotalsRowRange formula is changed.

Public Function wsNordics() As Worksheet
    Set wsNordics = ThisWorkbook.Worksheets("Nordics")
End Function

Public Function TableNordics() As ListObject
    Set TableNordics = wsNordics.ListObjects("TableNordics")
End Function

Public Function TargetAverage() As Double
    With TableNordics.ListColumns("Target")
        TargetAverage = WorksheetFunction.Subtotal(101, .DataBodyRange)
    End With
End Function

Public Function TableNordicsTargetAverage() As Double
    With TableNordics
        .ShowTotals = True
        With .TotalsRowRange
            TableNordicsTargetAverage = .Cells(, .Columns.Count)
        End With
    End With
End Function
user_99127140
Related