I'm new to VBA Excel and need some help creating some code.

I want to sum two columns of data; the number of rows with data is variable.

First sum=: cell F10 through the end of the range (Which I would normally
select by using shift/ctrl/down arrow). The Sum of this range needs to be in
cell G2.

Second Sum: The second sum needs to be the total of the range that starts 3
rows below (i.e. 2 blank rows) through the end of the data in that column
(Again, I use shift/ctrl/down arrow). This sum needs to be in cell G6.

Am having a tough time writing the code correctly to select the two
ranges....thanks for any hints you can provide.

David, experiment in VB with the .End method. Examples of its use a

varLastRow = Range("F10").End(xlDown).Row
varLastAddress = Range("F10").End(xlDown).Address
or try this
Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address

the .End takes one of 4 parameters: xlUp, xlDown, xlToLeft or xlToRight

You'll see this kind of thing used a lot also:
longLastUsedRow = Range("A" & Rows.Count).End(xlUp).Row
which will return the last used cell row number in column A. Assuming that
the last cell in column A (A65536 in Excel 2003) is empty, it would return
the row number of the first cell above it that was not empty.

"David B" wrote:

I'm new to VBA Excel and need some help creating some code.

I want to sum two columns of data; the number of rows with data is variable.

First sum=: cell F10 through the end of the range (Which I would normally
select by using shift/ctrl/down arrow). The Sum of this range needs to be in
cell G2.

Second Sum: The second sum needs to be the total of the range that starts 3
rows below (i.e. 2 blank rows) through the end of the data in that column
(Again, I use shift/ctrl/down arrow). This sum needs to be in cell G6.

Am having a tough time writing the code correctly to select the two
ranges....thanks for any hints you can provide.

Thanks for your reply:

This code:
Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address

looks to be very close to what I'm attempting, but there seems to be either
quotes or parentheses missing that I can't figure out.

"JLatham" wrote:

David, experiment in VB with the .End method. Examples of its use a

varLastRow = Range("F10").End(xlDown).Row
varLastAddress = Range("F10").End(xlDown).Address
or try this
Range("G2").Formula = "=SUM(F10:" & Range("F10").End(xlDown).Address

the .End takes one of 4 parameters: xlUp, xlDown, xlToLeft or xlToRight

You'll see this kind of thing used a lot also:
longLastUsedRow = Range("A" & Rows.Count).End(xlUp).Row
which will return the last used cell row number in column A. Assuming that
the last cell in column A (A65536 in Excel 2003) is empty, it would return
the row number of the first cell above it that was not empty.

"David B" wrote:

I'm new to VBA Excel and need some help creating some code.

I want to sum two columns of data; the number of rows with data is variable.

First sum=: cell F10 through the end of the range (Which I would normally
select by using shift/ctrl/down arrow). The Sum of this range needs to be in
cell G2.

Second Sum: The second sum needs to be the total of the range that starts 3
rows below (i.e. 2 blank rows) through the end of the data in that column
(Again, I use shift/ctrl/down arrow). This sum needs to be in cell G6.

Am having a tough time writing the code correctly to select the two
ranges....thanks for any hints you can provide.

Hi David -

If I understand the situation correctly, you have one set of numbers
starting at F10 and then another set of numbers that starts 2 rows below that.

There are MANY ways to solve this problem but here is a really easy way that
I use all the time. Try this:


Sub qwikSum()
Dim ws As Worksheet
Dim r As Range
Dim sum1 As Variant
Dim sum2 As Variant
Dim o As Integer

o = 0
sum1 = 0
sum2 = 0

Set ws = ThisWorkbook.Sheets(yourWorksheetName)
Set r = ws.Range("F10")

While r.Offset(o) < ""
If IsNumeric(r.Offset(o)) Then
sum1 = sum1 + r.Offset(o)
End If
o = o + 1
Wend

' That's sum 1

ws.Range("G2") = sum1

' Sum2 starts 2 rows below this
o = o + 2

While r.Offset(o) < ""
If IsNumeric(r.Offset(o)) Then
sum2 = sum2 + r.Offset(o)
End If
o = o + 1
Wend

' That's sum 2

ws.Range("G6") = sum2
End Sub


I like to call this the "Spider" approach as the sider goes down the column.
It is easy and can be used in a large number of contexts.

Hope that helps,

Chris (ct60)


"David B" wrote:

I'm new to VBA Excel and need some help creating some code.

I want to sum two columns of data; the number of rows with data is variable.

First sum=: cell F10 through the end of the range (Which I would normally
select by using shift/ctrl/down arrow). The Sum of this range needs to be in
cell G2.

Second Sum: The second sum needs to be the total of the range that starts 3
rows below (i.e. 2 blank rows) through the end of the data in that column
(Again, I use shift/ctrl/down arrow). This sum needs to be in cell G6.

Am having a tough time writing the code correctly to select the two
ranges....thanks for any hints you can provide.