Hi, So I have a huge excel file with about 5,000 rows. I need to insert two rows for everytime a month changes.

✔ Best Answer

First, a posting tip: Please click on the

How-To linkat the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.That said, I'm not sure what you mean by "There are 11 bank accounts" or how that impacts the layout of your data.

Based on the example given in Response #2, and assuming your dates start in E2, this code will insert 2 rows after each change of month and then insert monthly SUM formulas in Columns J & K.

Sub Insert_Formulas() Application.ScreenUpdating = False '*** Section of Code to Insert Blank Rows at Change of Month *** 'Determine last row with data in Column E lastRw = Cells(Rows.Count, "E").End(xlUp).Row 'Loop through data in reverse order For rw = lastRw To 3 Step -1 'When the Month changes, insert 2 Rows If Month(Cells(rw, "E")) <> Month(Cells(rw - 1, "E")) Then Range(Cells(rw + 1, "E"), Cells(rw, "E")).EntireRow.Insert End If Next '*** Section of Code to Store Formula at End of Monthly Data *** 'Determine new last row with data in Column E (after inserts) lastRw = Cells(Rows.Count, "E").End(xlUp).Row 'Set value for initial Formula Row firstForm_rw = 2 'Loop through data until a blank cell is found in Column J For nxtRw = rw To lastRw + 1 'When a blank cell is found, insert formulas in Columns J & K If Cells(nxtRw, "J") = "" Then With Cells(nxtRw, "J") .Formula = "=SUM(J" & firstForm_rw & _ ":J" & nxtRw - 1 & ")" .Font.Bold = True End With With Cells(nxtRw, "K") .Formula = "=SUM(K" & firstForm_rw & _ ":K" & nxtRw - 1 & ")" .Font.Bold = True End With 'Set value for next initial Formula Row & skip blank row firstForm_rw = nxtRw + 2 nxtRw = nxtRw + 1 End If Next End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

How is your data laid out? We need to know where the dates are and what they look like before we can offer a specific solution. Generically, write a macro that compares a date cell to the date cell below it. When the months don't match, insert 2 rows and then keep checking.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Hello so the dates are in column E

02/20/2017

02/20/2017

02/20/2017

02/27/2017

insert two rows

03/03/2017

03/03/2017

03/23/2017

03/23/2017

insert two rows

04/27/2017

04/27/2017

It is a large set of data with multiple bank accounts for every month there needs to be a reconciliation within each bank account. This is what I actually have to do:

There are 11 bank accounts

Bank Account

Column E (dates) F (num) G(name) J (credit) K(debit)

03/03/2017

03/03/2017

03/23/2017

03/23/2017

new row sum of all credits sum of all debits = J-K

blank row

04/27/2017

04/27/2017

04/27/2017

new row sum of all credits sum of all debits = J-K

blank row

The original data is

Bank Account

Column E (dates) F (num) G(name) J (credit) K(debit)

03/03/2017

03/03/2017

03/23/2017

03/23/2017

04/27/2017

04/27/2017

04/27/2017

Total amounts sum of all credits sum of all debits = J-K

First, a posting tip: Please click on the

How-To linkat the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.That said, I'm not sure what you mean by "There are 11 bank accounts" or how that impacts the layout of your data.

Based on the example given in Response #2, and assuming your dates start in E2, this code will insert 2 rows after each change of month and then insert monthly SUM formulas in Columns J & K.

Sub Insert_Formulas() Application.ScreenUpdating = False '*** Section of Code to Insert Blank Rows at Change of Month *** 'Determine last row with data in Column E lastRw = Cells(Rows.Count, "E").End(xlUp).Row 'Loop through data in reverse order For rw = lastRw To 3 Step -1 'When the Month changes, insert 2 Rows If Month(Cells(rw, "E")) <> Month(Cells(rw - 1, "E")) Then Range(Cells(rw + 1, "E"), Cells(rw, "E")).EntireRow.Insert End If Next '*** Section of Code to Store Formula at End of Monthly Data *** 'Determine new last row with data in Column E (after inserts) lastRw = Cells(Rows.Count, "E").End(xlUp).Row 'Set value for initial Formula Row firstForm_rw = 2 'Loop through data until a blank cell is found in Column J For nxtRw = rw To lastRw + 1 'When a blank cell is found, insert formulas in Columns J & K If Cells(nxtRw, "J") = "" Then With Cells(nxtRw, "J") .Formula = "=SUM(J" & firstForm_rw & _ ":J" & nxtRw - 1 & ")" .Font.Bold = True End With With Cells(nxtRw, "K") .Formula = "=SUM(K" & firstForm_rw & _ ":K" & nxtRw - 1 & ")" .Font.Bold = True End With 'Set value for next initial Formula Row & skip blank row firstForm_rw = nxtRw + 2 nxtRw = nxtRw + 1 End If Next End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Ask Your Question

Weekly Poll

Do you think Intel should make discrete graphics cards?

Discuss in The Lounge

Poll History