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.

 

[Fluff]

Could you please supply a sample of your data, both before & after?
Tools to help with that can be found here
https://www.mrexcel.com/forum/about-board/508133-attachments.html

 

[Jyotirmaya]

Could you please supply a sample of your data, both before & after?
Tools to help with that can be found here
https://www.mrexcel.com/forum/about-board/508133-attachments.html

SORRY FOR THE DELAY IN REPLY

BEFORE

AFTER

 

[Fluff]

Both those images are virtually unreadable.
Could you please post the actual data using one of the tools, in the link I supplied
Thanks

 

[Jyotirmaya]

Both those images are virtually unreadable.
Could you please post the actual data using one of the tools, in the link I supplied
Thanks

BEFORE

Excel 2013/2016

	A	B	C	D	E	F	G	H	I
1	SL
2	1						1	10	20
3							3	20	30
4	2						55	55	40
5							776	78	50
6							4444	888	60
7							7	9	70
8							89	55	80
9							0	55	90
10	3						66	30	100
11							656	35	110
12							65	40	120

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

BEFORE​

AFTER


Excel 2013/2016

	A	B	C	D	E	F	G	H	I
1
2
3
4
5	SL
6	1						1	10	20
7							3	20	30
8							TOTAL	30	50
9	2						55	55	40
10							776	78	50
11							4444	888	60
12							7	9	70
13							89	55	80
14							0	55	90
15							TOTAL	1140	390
16	3						66	30	100
17							656	35	110
18							65	40	120
19							TOTAL	105	330

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

AFTER​

Worksheet Formulas Cell Formula H8 =SUM(H6:H7) I8 =SUM(I6:I7) H15 =SUM(H9:H14) I15 =SUM(I9:I14) H19 =SUM(H16:H18) I19 =SUM(I16:I18) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[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
         Rng.Offset(Rng.Count).Resize(1).Value = "Total"
         With Rng.Offset(Rng.Count, 1).Resize(1, 10)
            .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
         Rng.Offset(Rng.Count).Resize(1).Value = "Total"
         With Rng.Offset(Rng.Count, 1).Resize(1, 10)
            .Formula = "=sum(" & Rng.Offset(, 1).Address(False, False) & ")"
         End With
      Next Rng
   End With
End Sub

Thanks this is working but I want some changes, as I want only H & I column's sum hence I have changed

With Rng.Offset(Rng.Count, 1).Resize(1, 10)

into

With Rng.Offset(Rng.Count, 1).Resize(1, 2)

One more change I want that, if after a Serial there is already a TOTAL Text & SUM of H & I Column is there then No need to again Insert the TOTAL & no need to sum again, it shouldn't change, as with this code if there are already TOTAL it counts again & Make it Double Total of the Values.

 

[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).Resize(1).Value = "Total"
            With Rng.Offset(Rng.Count, 1).Resize(1, 2)
               .Formula = "=sum(" & Rng.Offset(, 1).Address(False, False) & ")"
            End With
         Else
            Rng.Offset(Rng.Count).Resize(1).EntireRow.Delete
         End If
      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).Resize(1).Value = "Total"
            With Rng.Offset(Rng.Count, 1).Resize(1, 2)
               .Formula = "=sum(" & Rng.Offset(, 1).Address(False, False) & ")"
            End With
         Else
            Rng.Offset(Rng.Count).Resize(1).EntireRow.Delete
         End If
      Next Rng
   End With
End Sub

Everything is OK with this formula, but I got an error that if there are already Total then It don't calculate the Sum of H & I column, for example The Total is in G9 then the sum of H6+H7+H8 in H9 & I6+I7+I8 in I9 should be shown, but already there is a Total & wrongly it is showing the sum of H6+H7 in H9 & I6+I7 in I9, in this case this formula don't calculate the Column value.

 

[Fluff]

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.