I have this table I would like to loop through and change the subtotals to actual formulas.
My Thought was to loop through the A column because the subtotals are placed the row after the name. I thought if I could calculate the range between the last name I could use that to create a sum(). Any ideas on calculating that range?
The spreadsheet is much larger than that image.
Sub Cleanup() countblank 'Function to get lastrow Dim x As Integer Dim CRng Application.ScreenUpdating = False ' Set numrows = number of rows of data. NumRows = ctblank ' Select cell a1. Range("A2").Select ' Establish "For" loop to loop "numrows" number of times. For x = 2 To NumRows If (Not IsEmpty(Cells(x, 1)) And Not ActiveCell.Row = 2) Then sRegion = Range(Cells(x, 2).Offset(1, 0).Address).CurrentRegion LRow = Cells(x, 2).Address MsgBox LRow & "-" & Cells(x, 2).Offset(1, 0).Address LRow = "" 'ActiveCell.Offset(1, 3).Value = "=Sum(" & sRegion & ")" End If ActiveCell.Offset(1, 0).Select Next Application.ScreenUpdating = True End Sub