CODEWITHSUNDEEP
excelvba
Kurt
Kurt

Reputation: 135

Summing Dynamic Range Excel VBA

I am trying to sum values in an entire column which is not fixed.

Sub CTG()
Dim sht As Worksheet
Dim PreviousData As Long
Dim MySum As Double, ColLetter As String

Set sht = ThisWorkbook.Worksheets("Weekly")
PreviousData = sht.Cells(2, sht.Columns.Count).End(xlToLeft).Column - 1

ColLetter = Split(Cells(1, PreviousData).Address, "$")(1)
 

MySum = Application.WorksheetFunction.Sum(sht.Range("C:C"))

'MsgBox "The total of the ranges is " & MySum

End Sub

What I am trying to do is to convert

MySum = Application.WorksheetFunction.Sum(sht.Range("C:C"))

into

MySum = Application.WorksheetFunction.Sum(sht.Range("ColLetter :ColLetter"))

and I tried in several ways but it doesn't seem to be working.

Any help would be much appreciated. Thanks

Upvotes: 1

Views: 1205

Answers (1)

VBasic2008
VBasic2008

Reputation: 55073

Sum of a Column

  • The following sums up the values in the column before the last non-empty column calculated in the second row.
  • You have to know that Columns("C") = Columns(3) = Range("C:C").
Option Explicit

Sub SumOfColumn()
    
    Dim MySum As Double
    With ThisWorkbook.Worksheets("Weekly")
        Dim blCol As Long ' Column Before the Last Column
        blCol = .Cells(2, .Columns.Count).End(xlToLeft).Column - 1
        If blCol = 0 Then
            MsgBox "There is no column before column ""A"".", _
                vbCritical, "Sum of Column"
            Exit Sub
        End If
        MySum = Application.Sum(.Columns(blCol))
    End With
    
    MsgBox "The column's total is '" & MySum & "'.", _
        vbInformation, "Sum of Column"

End Sub
  • This has been addressed by BigBen in the comments (with sht.Columns(PreviousData) e.g. sht.Columns(3)).

Upvotes: 1

Related Questions