Hi,
Is there a way to get the following code to skip 3 rows when it finds the rngEnd to ensure it then defines the range as appropriate? The ranges vary within the sheet but there is always 3 clear rows from the end of block of data before the start of the next block of data.
Thanks,
EMcK
Is there a way to get the following code to skip 3 rows when it finds the rngEnd to ensure it then defines the range as appropriate? The ranges vary within the sheet but there is always 3 clear rows from the end of block of data before the start of the next block of data.
VBA Code:
Sub Test2()
Dim rngC As Range
Dim LastR As Long
LastR = Range("C" & Rows.Count).End(xlUp).Row
Set rngC = Range("C18:C" & LastR) 'Range("C4:C" & LastR)
For Each cell In rngC
If cell.Value = "" Then
Dim rngStart As String
Dim rngEnd As String
cell.Offset(1, 0).Select
rngStart = ActiveCell.Address
fr = Range(rngStart).Row
Selection.End(xlDown).Select
rngEnd = ActiveCell.Address
lr = Range(rngEnd).Row
i = lr - fr
cell.Offset(i + 3, 1).Formula = ("=SUM(D" & fr & ":D" & lr & ")")
cell.Offset(i + 3, 2).Formula = ("=SUM(E" & fr & ":E" & lr & ")")
cell.Offset(i + 3, 3).Formula = ("=SUM(F" & fr & ":F" & lr & ")")
cell.Offset(i + 3, 4).Formula = ("=SUM(G" & fr & ":G" & lr & ")")
cell.Offset(i + 3, 5).Formula = ("=SUM(H" & fr & ":H" & lr & ")")
cell.Offset(i + 3, 6).Formula = ("=SUM(I" & fr & ":I" & lr & ")")
cell.Offset(i + 3, 7).Formula = ("=SUM(J" & fr & ":J" & lr & ")")
cell.Offset(i + 3, 8).Formula = ("=SUM(K" & fr & ":K" & lr & ")")
cell.Offset(i + 3, 12).Formula = ("=SUM(O" & fr & ":O" & lr & ")")
cell.Offset(i + 3, 13).Formula = ("=SUM(O" & lr + 2 & "/J" & lr + 2 & ")*100")
End If
Next cell
End Sub
Thanks,
EMcK