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

Reply ↓  Report •

✔ Best Answer
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

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



#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.

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


Reply ↓  Report •

#2
November 28, 2017 at 13:07:33
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


Reply ↓  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 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


Reply ↓  Report •

Related Solutions

#4
November 28, 2017 at 18:08:00
✔ Best Answer
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

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


Reply ↓  Report •

Ask Question