CODEWITHSUNDEEP
excelvba
Sandeep Kushwah
Sandeep Kushwah

Reputation: 592

excel vba : Sum up column value to last column

Below is my excel format.

column KZ is Grand Total of all columns

A|B|C|D|E|F|G|H|.....|KZ|
2|4|3|5|5|6|2|7|.....|34|
8|2|3|5|6|6|3|7|.....|44|
2|0|3|5|5|6|2|7|.....|30|
9|4|3|6|5|6|2|7|.....|44|
2|4|5|0|5|6|3|2|.....|24|

There are 1000 of such rows in my excel sheet. I want to write a macro to sum all the column values to KZ. Please help

Here is the code I am trying : 

Sub SumMacro()

   Dim columns As Long

    columns = Sheets("AllData").UsedRange.Columns.Count
    Set testData = Sheets("AllData").Range("b65536").End(xlUp)
   ' Here I want to apply for/do while loop to sum up all the rows.
    ActiveCell.FormulaR1C1 = "=SUM(RC[-309]:RC[-1])"

End Sub

Problem : I am a beginner to VBA so I don't know how to select range dynamically.

Upvotes: 0

Views: 6447

Answers (3)

jones-chris
jones-chris

Reputation: 691

I tested this and it worked. It assumes you have a block of uninterrupted values and that KZ is the last column of that block.

Sorry if the region variable is a little wordy. I can clear it up for you if you like. 

    Sub getTotal()

    Dim cell As Range
    Dim region As Range
    Dim total As Integer

    Set region = Range("A1").CurrentRegion.Resize(Range("A1").CurrentRegion.Rows.Count, Range("A1").CurrentRegion.Columns.Count - 1)
    total = 0

    For Each cell In region
        total = total + cell.Value
    Next

    MsgBox (total)

    End Sub

Upvotes: 0

Sgdva
Sgdva

Reputation: 2800

This solves the stated situation -in general terms-, adjust to your ranges/specific desired result

  Sub SumMacro()

   Dim Totalcolumns As Long
   Dim CounterSum As Long
   Dim CounterColumn As Long
   Dim SumResult As Long
    Totalcolumns = Sheets("AllData").UsedRange.columns.Count

    For CounterColumn = 1 To Totalcolumns
    For CounterSum = 1 To Cells(Rows.Count, Totalcolumns).End(xlUp).Row
    On Error GoTo err01SumMacro
    SumResult = Cells(CounterSum, CounterColumn).Value + SumResult
    Next CounterSum
    Next CounterColumn
    MsgBox SumResult 'or debug print...
    If 1 = 2 Then '99 If error
err01SumMacro:
    MsgBox "err01SumMacro:Data in cells(R:" & CounterSum & ",C:" & CounterColumn & ") is not a number!", vbCriticalvbCritical
    End If

End Sub

Edit: Working code Sample

enter image description here

Upvotes: 1

David Andrei Ned
David Andrei Ned

Reputation: 809

I'm not sure why you want to use VBA when you can do a =SUM for each row using autofill.

You can also do a dynamic range without VBA, using the =OFFSET function which once again seems far easier than writing a macro for it.

However, if for whatever reason you insist on using VBA, check this link for whichever method makes more sense to you

Upvotes: 0

Related Questions