Hi,
I have this code that runs, and if the If criteria is met, it runs and writes in To Summary. Is it possible to capture the row reference of this cell, so it can be used in a formula, then this repeats?
I have this code that runs, and if the If criteria is met, it runs and writes in To Summary. Is it possible to capture the row reference of this cell, so it can be used in a formula, then this repeats?
Code:
Sub Summary()
FinalRow = ActiveSheet.Range("J65536").End(xlUp).Row
Dim HeadHeight As Double
Dim tHeight As Double
Dim n As Long
HeadHeight = Cells(1, 1).RowHeight
tHeight = 0
For n = 1 To ActiveSheet.UsedRange.rows.Count
tHeight = tHeight + Cells(n, 1).RowHeight
If tHeight > (752.25 + HeadHeight) Then
If Application.WorksheetFunction.Count(rows(n - 1 & ":" & n - 58)) > 0 Then
If Range("J" & n - 1).Value <> "To Summary" Or Range("O" & n).Value <> "" Then
Range("J" & n - 1).EntireRow.Select
Selection.Insert Shift:=xlDown
Range("J" & n - 1).Value = "To Summary"
tHeight = HeadHeight + Cells(n, 1).RowHeight
End If
End If
End If
Next n
End Sub