CODEWITHSUNDEEP
excelvba
Carlos80
Carlos80

Reputation: 433

VBA Sum column with variable row count

I have a column that I need to sum and store the total as a variable. I have looked on this site and online and tried to adapt my syntax to how I believe it should work but still with no luck.

I was wondering if someone might be able to point out where I am going wrong.

Dim RowCount As Integer
Dim Total_TIV As Long

With Sheets("Input")
    RowCount = WorksheetFunction.CountA(Range("C18", Range("C18").End(xlDown)))
End With

With Sheets("Input")
    Total = WorksheetFunction.Sum("S18" & ":S" & 18 + RowCount)
End With

Upvotes: 0

Views: 1198

Answers (2)

Scott Craner
Scott Craner

Reputation: 152505

just return the row at the bottom of the range.

You only need one with block.

You need to append the ranges with . to indicate it belongs to the parent.

The SUM requires a range object not a string.

Dim RowCount As Long
Dim Total As Double

With Sheets("Input")
    RowCount = .Range("C18").End(xlDown).Row
    Total = WorksheetFunction.Sum(.Range("S18:S" & RowCount))
End With

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

SUM() needs a range (just like CountA()):

  Total = WorksheetFunction.Sum(Range("S18" & ":S" & (18 + RowCount)))

Upvotes: 0

Related Questions