VBA code to sum dynamic range below a cell

[LtCmdrData]

I have 2 columns of data - first column is item and the second column is the count of that item. The number of rows will vary each time the report is run. I have vba code to place "Grand Total" in row 4 of column Q. I need vba code to sum the total of the second column starting in row 5 and going down a dynamic range and place that total in row 4 of column R so it lines up with the Grand Total. Is there a way to utilize CurrentRegion? I appreciate any help someone could offer. Thanks.

Book1
	Q	R
4	Grand Total	SUM goes here
5	A	959,973
6	B	338,125
7	C	241,641
8	D	156,772
9	E	50,232
10	F	37,352
11	H	25,080
12	I	18,785
13	J	13,223
14	K	11,204
15	L	9,735
16	M	6,361
17	N	5,748
18	O	4,266
19	P	4,214
20	Q	4,180
21	R	4,145
22	S	3,956
Sheet1

 

[Joe4]

Here is one way:

Sub AddGrandTotal()
    
    Dim lr As Long
    
'   Find last row with data in column R
    lr = Cells(Rows.Count, "R").End(xlUp).Row
    
'   Put sum formula in cell R4
    Range("R4").Formula = "=SUM(R5:R" & lr & ")"
    
End Sub

 

[davesexcel]

try this

Range("R4") = Application.Sum(Range("R5:R" & Cells(Rows.Count, "R").End(xlUp).Row))

 

[Joe4]

Just want to point out a difference in the two replies (which may or may not matter to you).
My solution will place a SUM formula in cell R4.
Dave's solution will just place the value of the sum (hard-coded number) in cell R4.

You may not care, unless you need it to to be dynamic, and change automatically if the existing values in column R may change (and you want the sum to change accordingly).
But you should just be aware of the difference, and do whatever suits your needs best.

 

[LtCmdrData]

Just want to point out a difference in the two replies (which may or may not matter to you).
My solution will place a SUM formula in cell R4.
Dave's solution will just place the value of the sum (hard-coded number) in cell R4.

You may not care, unless you need it to to be dynamic, and change automatically if the existing values in column R may change (and you want the sum to change accordingly).
But you should just be aware of the difference, and do whatever suits your needs best.

Thank you for taking the time to point out the difference to me. In this instance the other solution works because hard-coding is fine for this application but now I have an alternative if circumstances change.

 

[LtCmdrData]

try this

Range("R4") = Application.Sum(Range("R5:R" & Cells(Rows.Count, "R").End(xlUp).Row))

Thank you for your help, this is just want I needed.