# Thread: Totalling a variable column of numbers

1. I am writing a macro that does formats a download of data that we have so it is as our Client's require. I am very proud of this macro except I cannot get it to total some columns.

The columns are always in the same place (BI and BJ). The macro inserts a column after BJ and then I need it to:

Add BI2 and BJ2 together into BK
Copy the addition formula all the way down to the last row (which varies on each download - could be 2, could be thousands).
Add a total to the bottom of BI, BJ and the new BK

The macro then does some other stuff. I have managed in a very clunky way to get the first two sentences above to work, but it is very clunky. I cannot get the last sentence at all as it will not adjust according to the number of rows.

Can anyone help me as I am sure I am missing something fundamental, I can do this easily when working live but my macro will not replicate even though I am careful with my relative and absolute referencing. I am not a developer, I am an advanced excel user who helps out others in my Company but the word 'advanced' is subjective obviously.

Cheers
Kate

2. Have you tried the USEDRANGE function?

cheers, Paul

Dim LastRow As Long
LastRow = Application.WorksheetFunction.Max _
(Cells(Rows.Count, 9).End(xlUp).Row, _
Cells(Rows.Count, 10).End(xlUp).Row)

Cells(LastRow + 2, 9).Formula = "=Sum(I2:I" & LastRow & ")"
Cells(LastRow + 2, 10).Formula = "=Sum(J2:J" & LastRow & ")"

Cells(LastRow + 2, 11).Formula = "=If(Sum(I" & LastRow + 2 & _
":J" & LastRow + 2 & ")<>sum(K2:K" & LastRow & _
"),""Check"",Sum(I" & LastRow + 2 & ":J" & LastRow + 2 & "))"
End Sub
'--
Jim Cone
Portland, Oregon USA

4. Kate,

Try:
Code:
```[BK2].Select
Do While ActiveCell.Offset(0,-2).Value <> ""
ActiveCell.FormulaR1C1="=RC[-2]+RC[-1]"
ActiveCell.Offset(1,0).Select
Loop```

5. Originally Posted by RetiredGeek
Kate,

Try:
Code:
```[BK2].Select
Do While ActiveCell.Offset(0,-2).Value <> ""
ActiveCell.FormulaR1C1="=RC[-2]+RC[-1]"
ActiveCell.Offset(1,0).Select
Loop```
That's real ugly code - all those unnecessary Select and Offset operations!

6. Originally Posted by macropod
That's real ugly code - all those unnecessary Select and Offset operations!
Macropod - I was a bit surprised by the peremptory tone of your post! As a Silver Lounger/Moderator addressing a Four-star Lounger in a public forum, perhaps you could just have pointed out that Jim Cone's solution would process more efficiently by avoiding the need to select each cell in turn - making essentially the same point, but in a less pejorative manner.

Whenever I post a question I browse to see if I can answer any outstanding posts by way of doing my bit in return; but I usually find someone's got there before me, and rarely find unanswered posts, let alone any that I can help with. I'm sure if I do post a solution there is likely to be someone who knows better, but in that case would prefer to be pointed in the right direction than just told I have it wrong.

Cheers

Alison C

7. Hi Alison,

My comments certainly should not be taken as perjorative - they were intended in good humour, hence the '!'.

8. I appreciate your help but I cannot get it to work. I am unable to post the whole macro up (It is long and clunky, as I said I am not a developer and am reluctant to 'clean' code until it works but more importantly it has confidential info in it) but I can show the preceeding and following code:

Preceding
Range("BK1").Select
Selection.EntireColumn.Insert
ActiveCell.FormulaR1C1 = "Grand Total"
With ActiveCell.Characters(Start:=1, Length:=11).Font
.FontStyle = "Bold"
End With
Range("BK2").Select

Following
Range("BL1:BV1").Select
Selection.EntireColumn.Hidden = True
Range("BW1:BX1").Select
Selection.EntireColumn.Delete

What am I doing wrong? Appreciate your help, I am learning

9. Based on Jim's code and yours:

Code:
```****With Range("BK1")
****** .EntireColumn.Insert
****** .Value = "Grand Total"
****** .Font.Bold = True
****End With
****
****Dim LastRow As Long
** * ' get highest used row number from cols BI and BJ
****LastRow = Application.WorksheetFunction.Max _
****(Cells(Rows.Count, "BI").End(xlUp).Row, _
****Cells(Rows.Count, "BJ").End(xlUp).Row)
****
****' add sum formulas to BK
****Range("BK2:BK" & LastRow).FormulaR1C1 = "=Sum(RC[-2]:RC[-1]"
****' add totals to the row beneath the last row for BI:BK
****Range("BI" & LastRow + 1).Resize(, 3).FormulaR1C1 = "=SUM(R2C:R[-1]C)"

****Range("BL1:BV1").EntireColumn.Hidden = True
****Range("BW1:BX1").EntireColumn.Delete```

10. Alison, as a fellow antipodean you should hardly have noticed the direct response of macropod.

cheers, Paul

11. You're right, I should by now be used to direct talking with no unnecessary words - must remember it's all said in the !

Cheers

Alison

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•