Inserting Rows Base on Data Above or Below Row

[Big_Chew]

Hey guys,

I am looking for help with some VBA code to insert a row based on the data either above or below the row. In the data set I have, column A contains the year and column B contains the month. I can have more than two rows of the same month and year, so I may have more than 12 rows containing the same year. What I am wanting to achieve is insert one row (two would be best) between years 2020 and 2021 and 2022 and so on. Can anyone help?

 

[Fluff]

Hi & welcome to MrExcel.
How about

Sub BigChew()
   Dim i As Long
   
   For i = Range("A" & Rows.Count).End(xlUp).Row To 3 Step -1
      If Cells(i, 1) <> Cells(i - 1, 1) Then Rows(i).Resize(2).Insert
   Next i
End Sub

 

[Big_Chew]

Yes, I think that will work. I tried it in a test workbook.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Big_Chew]

After further testing I have a slight issue.

How do I get the code to only run a certain sheet?

 

[Fluff]

What is the name of the sheet?

 

[Big_Chew]

Adjusted

 

[Fluff]

Ok, how about

Sub BigChew()
   Dim i As Long
   
   With Sheets("Adjusted")
      For i = .Range("A" & Rows.Count).End(xlUp).Row To 3 Step -1
         If .Cells(i, 1) <> .Cells(i - 1, 1) Then .Rows(i).Resize(2).Insert
      Next i
   End With
End Sub

 

[Big_Chew]

Thank you so much. I tried other code I had to only focus on the Adjusted which worked with other equations but I could not get it to work with the insert cells.

 

[Fluff]

You're welcome & thanks for the feedback.