Sum Cell Values Until Blank Cells With VBA Help.

punnipah

Board Regular
Joined
Nov 3, 2021
Messages
134
Office Version
  1. 2019
Platform
  1. Windows
Hi

i want to Sum Cell Values Until Blank Cells
This my Code But Not working please advise.

i want to sum in Columns Value "R" and Value "S" when Blank Cells

Sub sum()

Dim cell As Range, rngSum As Range, rngData As Range
Dim ws As Worksheet

Set ws = ActiveWorkbook.Sheets("RPA")
Set rngData = ws.Range("R4", ws.Cells(Rows.Count, "R").End(xlDown))

For Each cell In rngData
If cell = "" Then
Set rngSum = ws.Range(cell.Offset(1), cell.Offset(1).End(xlDown))
cell = "=SUM(" & rngSum.Address(0, 0) & ")"
End If


1669185378358.png
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
I would approach like this:
VBA Code:
Sub mySum()
  Dim lRow As Integer, cell As Integer
  Dim rng As Range
  lRow = Cells(Rows.Count, 18).End(xlUp).Row
  
  For i = 5 To lRow
    cell = Cells(i, 18).End(xlDown).Row + 1
    Set rng = Range("R" & i & ":R" & (cell - 1))
    Cells(cell, 18).Value = "=SUM(" & rng.Address(0, 0) & ")"
    Cells(cell, 19).Value = "=SUM(" & rng.Offset(, 1).Address(0, 0) & ")"
    i = cell
  Next
End Sub
 
Upvote 0
I'm not sure why this row in your sample was not marked?

1669202231644.png


In any case, try this with a copy of your workbook.
I have assumed that the numbers in column R are not the result of formulas. If they are, please advise details.

VBA Code:
Sub SumBlocks()
  Dim rA As Range
  
  For Each rA In Range("R4", Range("R" & Rows.Count).End(xlUp)).SpecialCells(xlConstants).Areas
    rA.Offset(rA.Rows.Count).Resize(1, 2).FormulaR1C1 = "=SUM(R" & rA.Row & "C:R[-1]C)"
  Next rA
End Sub
 
Upvote 0
Solution
I would approach like this:
VBA Code:
Sub mySum()
  Dim lRow As Integer, cell As Integer
  Dim rng As Range
  lRow = Cells(Rows.Count, 18).End(xlUp).Row
 
  For i = 5 To lRow
    cell = Cells(i, 18).End(xlDown).Row + 1
    Set rng = Range("R" & i & ":R" & (cell - 1))
    Cells(cell, 18).Value = "=SUM(" & rng.Address(0, 0) & ")"
    Cells(cell, 19).Value = "=SUM(" & rng.Offset(, 1).Address(0, 0) & ")"
    i = cell
  Next
End Sub
Thank you very much
 
Upvote 0
Thank you very much
The marked solution has been changed accordingly.
In your future questions, please mark the post as the solution that answered your question instead of your feedback message as it will help future readers. No further action is required for this thread.
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,268
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top