Addition between two defined cells

suprsonic · #1 (**permalink**) February 18th, 2011, 12:52 PM

I work with long spreadsheets that vary in length but never more than 30,000 rows. Within these rows are some empty rows and a header, which all need to be ignored. Because of these conditions I am trying to define a range from top to bottom of each column. I have the code written to the point that I am ready to sum (or avg) between the two defined cells "toprow" & "bottomrow". I can't get the code to perform the calculation.
Below is what I have so far. The last line is where I'm stuck.

Sub macro()

col = Selection.Column
Application.Goto reference:="r10C"
Selection.End(xlUp).Select
toprow = ActiveCell.Value
Application.Goto reference:="r30000C"
Selection.End(xlUp).Select
bottomrow = ActiveCell.Value
ActiveCell.Offset(3, 0).Select

ActiveCell.Formula = "=Sum.range(toprow:bottomrow)"

End Sub

mtranchi · #2 (**permalink**) February 18th, 2011, 09:06 PM

you can use the worksheetfunction method and just put in the entire column as excel will ignore rows below the used range

Code:

'When you use the With statement, anything you start with a period will refer to that object. Note that the range object starts with a period.
With Sheets("MySheet")
    'Sums all entries in column A
    WorksheetFunction.Sum(.Range("A:A"))
End with

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
ADODB.Connection user-defined type not defined	Wall st Guru	Excel VBA	2	March 26th, 2014 03:44 PM
Hexadecimal Addition	madanshah16	XSLT	1	July 31st, 2007 07:21 AM
Appl-defined or Object-defined error 1004	chp	Excel VBA	3	April 4th, 2006 08:12 AM
User-defined type not defined (Icecream.mdb)	dloren01	BOOK: Beginning Access VBA	0	June 22nd, 2005 10:36 PM
Application-defined or object-defined error	James	Excel VBA	1	August 12th, 2003 11:50 PM