Macro for Summing Rows above blank cell until previous blank cell

[RKK1699BGC]

Hi all VBA pro's.

I don't code VBA per se but rather record macro's or help search the forum for code that I can copy and tweak as necessary.

Think I am nearly there with this query having extensively searched forum but just can't find exactly what I require.

I have numerical data going from C10:AC600 and I already have got a code to insert blank rows where required.

So the below code does works BUT I have the following 2 issues that I kindly request anyone's help on:

i) How do I extend the code to perform same "Sum" across all columns up to AC?
ii) How can I get the code to show "Sum" formula rather than hard numbers? Seems I need to add something like this, .Formula = "=sum(" & Rng.Address & ")"


Dim ws As Worksheet
Set ws = Worksheets("sheet1")

Dim FirstCell As Range
Set FirstCell = ws.Range("C10")

Dim VeryLastCell As Range
Set VeryLastCell = ws.Cells(ws.Rows.Count, "C").End(xlUp)

Do
Dim LastCell As Range
If FirstCell.Offset(1) = vbNullString Then
Set LastCell = FirstCell
Else
Set LastCell = FirstCell.End(xlDown)
End If

With LastCell.Offset(1, 0)
.Value = Application.WorksheetFunction.Sum(ws.Range(FirstCell, LastCell))
.Interior.Color = RGB(255, 255, 0)
End With

Set FirstCell = LastCell.Offset(2, 0)

Loop While FirstCell.Row < VeryLastCell.Row
End Sub


Any help much appreciated.

Tks,
Nick

[AlphaFrog]

Please Login or Register  to view this content.

[RKK1699BGC]

That is absolute magic! Thank you.