Calculating Average once blank row is added

Hi, after I add in the 2 lines once it finds differences in Column D, I want to average the cell values in Column G and H.

So it adds 2 blank lines once it finds a difference, then on the first added blank line, I want the average to be calculated automatically for Columns G and H. Where can i add it to the code below?

Code below:

Sub InsertLine()
Dim lRow As Long
For lRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row To 2 Step -1
If Cells(lRow, "D") <> Cells(lRow - 1, "D") Then Rows(lRow).EntireRow.Insert
Next lRow
End Sub

Thanks!

Originally Posted by LokeshKumar

Can you upload the workbook....there would be more simpler code available....

Yup, see attached. Thanks

Hi Branbran10,

The following Macro may help you. The addition of blank lines or the deletion of blank lines should not affect the Excel Average formula:

Sub CalculateAverage()

    Dim lRow As Long
    Dim xAverageG As Double
    Dim xAverageH As Double
    
    Dim sAverageCellG As String
    Dim sAverageCellH As String
    
    Dim sRangeG As String
    Dim sRangeH As String
    
    'Use Column 'D' to calculate the Last Row Number
    lRow = Cells(Cells.Rows.Count, "D").End(xlUp).Row
    
    'Generate Ranges for Column 'G' and Column 'H'
    sRangeG = "G2:G" & lRow
    sRangeH = "H2:H" & lRow
    
    'To calculate the average
    xAverageG = Application.WorksheetFunction.Average(Range(sRangeG))
    xAverageH = Application.WorksheetFunction.Average(Range(sRangeH))


    'To put the average in a formula
    sAverageCellG = "G" & (lRow + 1)
    sAverageCellH = "H" & (lRow + 1)
    
    Range(sAverageCellG).Formula = "=Average(" & sRangeG & ")"
    Range(sAverageCellH).Formula = "=Average(" & sRangeH & ")"
    
End Sub

Lewis

This creates two empty rows to be added to the top.. how can that be fixed?

Beautiful, thank you!