VBA code to add blank row when change in data occurs in specific columns

VBA Help

New Member
Joined
Jul 31, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Hello,

Sorry if this post has already been written before but I am very new to Macros and am having trouble finding a code that works the way I would like it. So I have a payroll report that I run all the time and I ran a simple macro to get the formatting down to which columns I need but I am trying to also have the Macro add a blank row when the data changes in columns C, G, and M. One other thing, row 1 of my data is the title of each column and I do not want to add a blank line between the title of each column and the first of my data. So for column C, it is expense type of Salary or Fringe. I just want a blank row to be added when a row changes from Salary to Fringe or from Fringe to Salary. For column G, it is the account number. Same thing, I would like to add a blank row when there is a change from one account to another. And column M is employee name. Anytime there is a change in the employees name, I would like a blank row to be added.

One last thing, is it possible to write the code to only add a blank row when there is a change in data and not from another blank row? I worry that if there is a code to add a blank row when there is a change from Salary to Fringe, it will add another blank line when the next columns code is looking through the change in account numbers. And once again when the code is looking for a change in employee names. I hope that makes sense. Attached is mock version of the file I am working on. If you look at the attachment, I would like the code to add the blank rows like I have done. Column G and Column M both had a change in data but only one blank row is added. The next blank row that is added is for the name change in Column M.

Any help would be appreciated. I will do my best to answer any questions but I am not the most tech savvy.
 

Attachments

  • Test Payroll.PNG
    Test Payroll.PNG
    24.3 KB · Views: 3

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
As long as you only have Salary or Fringe in column C does the below do what you want?
Make sure that you test it on a copy of your worksheet.

VBA Code:
Sub InsertBlankRows()
Dim LastRow As Long, i As Long
    Application.ScreenUpdating = False

    LastRow = Cells(Rows.Count, "C").End(xlUp).Row

    For i = LastRow To 3 Step -1
        If Cells(i, "C") <> Cells(i - 1, "C") Or _
           Cells(i, "G") <> Cells(i - 1, "G") Or _
              Cells(i, "M") <> Cells(i - 1, "M") Then _
                Rows(i).Insert
    Next i

    Application.ScreenUpdating = True
End Sub
 

VBA Help

New Member
Joined
Jul 31, 2020
Messages
4
Office Version
  1. 2016
Platform
  1. Windows
Thank you so much this is exactly what I was looking for. I'm sure I will be asking more questions eventually to see if there are more advanced Macros to help with my data clean up. But this is perfect.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,886
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
Happy it helped and welcome to the Forum.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,493
Messages
5,601,998
Members
414,490
Latest member
Rip181

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top