Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Using Macro to automatically sum a variable range of rows?

  Asked By: Bill    Date: Feb 19    Category: MS Office    Views: 5011
  

I am trying to create a macro that will automatically sum a variable
range of rows. Simple example.

Price Qty

1 10
4 20
6 30
10 40

------
5 7
17 10
19 4
25 3
30 1
36 0

The rows of data will always start on the same row so I have tried
just doing a CRTL+ down arrow. How do you convey, in VB Excel, to
move down 2 additional rows and then sum the range above?

It would be interesting to see some code for doing this if the start
row is different but that's not in the immediate focus of my question.

I tried doing this by recording the macro and doing the keystrokes
necessary to get the result, but I am at a loss on what to do to edit
the VB code of the macro to achieve my goal so it works on variable
row ranges.

Share: 

 

1 Answer Found

 
Answer #1    Answered By: Tia Hughes     Answered On: Feb 19

If I understand what you want to do I do something similar without
going into VBA.

=SUM(OFFSET($A$1,0,0,ROW()-2,1))

... Will sum  everything in column A from A1 to 2 rows  above the cell
the formula is in.
If you add rows in between it don't matter... the formula takes care
of that.

 
Didn't find what you were looking for? Find more on Using Macro to automatically sum a variable range of rows? Or get search suggestion and latest updates.




Tagged: