SUM TILL THE LAST DATA

[anup47]

Hi Everybody


I'm looking for a macro which can sum all the data in a range till a blank cell arrives.

For example my data set starts from b2 to b11, and then b15 to b20, now i need to sum at c2 sum(b2:b11) and so on.


I need a macro, when i select c2 and press the macro shortcut should sum(b2:b11) and when i select c15 should sum(b15:b20), i have got huge data list and no. of data in each list is different.


Regards

Anup

 

[Hui]

Anup


have you looked at using Subtotal ?


you may need to add a helper Column with a formula like

lets say its Column F

F2: =IF(E2="","",IF(E1="",MAX($F$1:F1)+1,F1))

Copy down and paste as values

Run the Subtotal using this helper column

 

[anup47]

thank you for your reply

I would make my question a little simpler.

I've tried a macro


ActiveCell.FormulaR1C1 = "=MAX(RC[-1]:R[10]C[-1])"


here i just want that R[10] should be R[LAST CELL BEFORE THE EMPTY CELL],

It can be R[5] If 5th row contains the last data, i.e. 6th is empty.


Please suggest some trick to do this.

regards

Anup

 

[Luke M]

[pre]

Sub QuickSum()
'Creates formula to sum continuous block of cells left of active cell
Dim LastRow As Integer
LastRow = ActiveCell.Offset(0, -1).End(xlDown).Row - ActiveCell.Row
ActiveCell.FormulaR1C1 = "=SUM(RC[-1]:R[" & LastRow & "]C[-1])"
End Sub

[/pre]

 

[anup47]

hi luke

thanks for he tip, i have worked on it and added some more logic and now its working for me.

i'm just writting the vba here.


Dim LastRow As Integer

LastRow = ActiveCell.Offset(0, -1).End(xlDown).Row - ActiveCell.Row

ActiveCell.FormulaR1C1 = "=MAX(RC[-1]:R[ & LastRow & ]C[-1])"

ActiveCell.Offset(0, 1).Select

LastRoww = ActiveCell.Offset(0, -2).End(xlDown).Row - ActiveCell.Row

ActiveCell.FormulaR1C1 = "=MIN(RC[-2]:R[ & LastRoww & ]C[-2])"

ActiveCell.Offset(0, 1).Select

LastRowww = ActiveCell.Offset(0, -3).End(xlDown).Row - ActiveCell.Row

ActiveCell.FormulaR1C1 = "=average(RC[-3]:R[ & LastRoww & ]C[-3])"


thanks once again, its really working awesome


Regards

Anup

 

[Luke M]

Cool.

FYI, I don't think you needed to use the additional variables of LastRoww and LastRowww. It appears they are all defined by the same thing, aka, what LastRow was defined as.