Auto Insert Row & Sum the Above Cells Excel Macro

[Jyotirmaya]

In my Excel sheet I have values from Column A to Q. Column A is the Serial number Such as 1 2 3.....starting values from A6 as 1.
Column G is filled in without having Blank rows.
Example Column A6 is 1 & Again column A8 is 2.
Hence I want to insert a Row in 8th row & will have a Text TOTAL in G8 & it will Sum the values of H6+H7 & the Sum will be shown on H8 & similarly sum of I6+I7 & the sum will be shown on I8.


Column A data is filled in up to 5000 rows & Column G data also filled in upto 5000 rows. I want that if in the G column there are already "TOTAL" then it will just calculate the sum of H & I. & If there are no TOTAL then it will have the text TOTAL & will sum.

 

[Jyotirmaya]

Sorry but I don't understand what you're saying. If the word total is in G9 then the code I supplied doesn't do anything to the formulae in that row.

Yes you are right, if the word Total is there the code doesn't do anything, but I have some errors in my Database, for Example there are Some Total Texts are there where it wrongly sums two cells instead of Three cells. In that case the code doesn't do anything as Total text is there. Hence if the Total text is there it should recalculate.

 

[Fluff]

How about

Sub InsertTotals()

   Dim Rng As Range
   
   With Range("A7:A" & Range("G" & Rows.Count).End(xlUp).Row)
      .SpecialCells(xlConstants).EntireRow.Insert
   End With
   
   With Range("G6", Range("G" & Rows.Count).End(xlUp))
      For Each Rng In .SpecialCells(xlConstants).Areas
         If Rng.Offset(Rng.Count - 1).Resize(1).Value = "Total" Then Rng.Offset(Rng.Count - 1).Resize(1).EntireRow.Delete
         Rng.Offset(Rng.Count).Resize(1).Value = "Total"
         With Rng.Offset(Rng.Count, 1).Resize(1, 2)
            .Formula = "=sum(" & Rng.Offset(, 1).Address(False, False) & ")"
         End With
      Next Rng
   End With
End Sub

 

[Jyotirmaya]

How about

Sub InsertTotals()

   Dim Rng As Range
   
   With Range("A7:A" & Range("G" & Rows.Count).End(xlUp).Row)
      .SpecialCells(xlConstants).EntireRow.Insert
   End With
   
   With Range("G6", Range("G" & Rows.Count).End(xlUp))
      For Each Rng In .SpecialCells(xlConstants).Areas
         If Rng.Offset(Rng.Count - 1).Resize(1).Value = "Total" Then Rng.Offset(Rng.Count - 1).Resize(1).EntireRow.Delete
         Rng.Offset(Rng.Count).Resize(1).Value = "Total"
         With Rng.Offset(Rng.Count, 1).Resize(1, 2)
            .Formula = "=sum(" & Rng.Offset(, 1).Address(False, False) & ")"
         End With
      Next Rng
   End With
End Sub

This is perfect, Thanks

 

[Fluff]

Glad to help & thanks for the feedback