Neil Tenbrook

I'm trying to capture the sum of a highlighted range in an Excel Marco without using a loop. After selecting a range, I can capture the cell count with the following command:

X = Range(Selection, Selection.End(xlDown)).Count

Wondering if there is way to the sum as well. Excel can show the sum highlighted range, so it seems I should be able to replecate in VB.



Re: Excel-Capture the sum of Highlighted Range


See this thread: PostID=1493182&SiteID=1

It should be applicable to your scenario also.

Re: Excel-Capture the sum of Highlighted Range

Neil Tenbrook

Thanks. Your idea help me think of some different approaches and I arrived at these lines of code:

Range(Selection, Selection.End(xlDown)).Select
y = Application.WorksheetFunction.Sum(Selection)

Any thoughts on what I have above I've used CountA in the past to capture number of rows, but the function seems to create big files.

I appreciate you taking the time to respond to my post.

Re: Excel-Capture the sum of Highlighted Range

Andy Pope


You could try something like this. Usually you do not need to select ranges in order to reference them.

Code Snippet

MsgBox Application.WorksheetFunction.Sum(Range("I2:I" & Range("I2").End(xlDown).Row))
MsgBox Application.WorksheetFunction.Count(Range("I2:I" & Range("I2").End(xlDown).Row))
MsgBox Range("I2:I" & Range("I2").End(xlDown).Row).Rows.Count