# Solved How to insert a rows after date is changed

November 28, 2017 at 11:50:16
Specs: Windows 10
 Hi, So I have a huge excel file with about 5,000 rows. I need to insert two rows for everytime a month changes. See More: How to insert a rows after date is changed November 28, 2017 at 18:08:00
 First, a posting tip: Please click on the How-To link at 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 ```

#1 November 28, 2017 at 12:26:18
 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.

Report •

#2
November 28, 2017 at 13:07:33
 Hello so the dates are in column E 02/20/201702/20/201702/20/201702/27/2017insert two rows 03/03/201703/03/201703/23/201703/23/2017insert two rows 04/27/201704/27/2017

Report •

#3
November 28, 2017 at 13:21:39
 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 accountsBank Account Column E (dates) F (num) G(name) J (credit) K(debit) 03/03/2017 03/03/2017 03/23/2017 03/23/2017new row sum of all credits sum of all debits = J-K blank row 04/27/2017 04/27/2017 04/27/2017new row sum of all credits sum of all debits = J-K blank rowThe 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/2017Total amounts sum of all credits sum of all debits = J-K

Report •

Related Solutions

#4 November 28, 2017 at 18:08:00
 First, a posting tip: Please click on the How-To link at 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 ``` 