Excel adding multiple rows

Question

IS there a quick way to do this?

For example I have two rows

A B

34 5
56 6
34 3
25 2

I want to do the following calculation down the rows.

A1 = A1+B1
A2 = A2+B2
A3 = A3+B3 ..
..

Now I can do this with a macro that loops though the rows like

for x = 1 to 500
sheet1.cells(x,1).vlaue = sheet1.cells(x,1).vlaue + sheet1.cells(x,2).vlaue
next x

but it there a more efficient way by using ranges or something where it can be carried out as a single step?

Cheers

is there a specific reason why you want to overwrite the first coloum with the result? — Legionair
– Legionair, Commented Jul 29, 2012 at 0:10
yes. the first collum is a current value, and the second is the step value. So each time a user clicks a button to evulate the sheet. some totals are updated and the step values are added to the "current values" to give the new "current values". Yes I could do it with three coloums, current, step, and current+step. But I would prefer not to. — DevilWAH
– DevilWAH, Commented Jul 29, 2012 at 0:15

lori_m · Accepted Answer · 2012-07-29 11:26:05Z

3

A quick way is to enter from the immediate window:

[a1:a500]=[a1:a500+b1:b500]

The square brackets are a shortcut for the Evaluate function

answered Jul 29, 2012 at 11:26

lori_m

5,5751 gold badge21 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DevilWAH Over a year ago

This is about perfect for what I want. Very nice and simple.

chris neilsen · Accepted Answer · 2012-07-29 01:42:02Z

I can think of two options:

Option 1

Use Copy, Paste Special, Add

Sub AddStep()
    Dim rng1 As Range, rng2 As Range

    Set rng1 = Range([A1], [A1].End(xlDown))
    Set rng2 = rng1.Offset(, 1)
    rng2.Copy
    rng1.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
    Application.CutCopyMode = False

End Sub

Option 2

Loop over a variant array. This is much faster than looping over a range

Sub AddStep()
    Dim rng As Range
    Dim dat As Variant
    Dim i As Long

    Set rng = Range([B1], [A1].End(xlDown))
    dat = rng
    For i = LBound(dat, 1) To UBound(dat, 1)
        dat(i, 1) = dat(i, 1) + dat(i, 2)
    Next
    rng = dat
End Sub

Option 1 is faster, but in some circumstances it is best to avoid the clipboard.
Tested on a sample of 1,000,000 rows:
Option 1 - 435ms
Option 2 - 2589ms

KVISH · Accepted Answer · 2012-07-29 00:02:35Z

0

The way I would do it is create a formula for the first row, then just select all the rows after and press Ctrl+D. Isn't this what you are after? Unless I misunderstood the question.

answered Jul 29, 2012 at 0:02

KVISH

13.3k18 gold badges90 silver badges171 bronze badges

1 Comment

DevilWAH Over a year ago

you cant do a formula in cell A1 of = A1+B1, this would be a cyclic formula where you are using the destination cell as an argument for the calculation. You have to use some VB code to keep it tidy. Trouble with using loops to loop though the rows is it is not efficient, some programming languages allow you preform calculations on arrays in a much more efficient manor. Wondering if this can be done in excel to.

Collectives™ on Stack Overflow

Excel adding multiple rows

3 Answers 3

1 Comment

Option 1

Option 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Option 1

Option 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related