VBA help - New row in excel with predefined data

[aka1999]

Hi all,
Is there a way to add a new row with same data everytime I encounter Row ID 5 in the 3rd column per the below screenshot?
I want to add a row with Row ID 6 and Name NewCity.
I have a similar data but with about 100 countries, more columns and have to add particular set of data for all the countries. I am very new to this so that's why asking for a solution for this one if any one can help.
Thank you!

31015

[Paul_Hossler]

Welcome to the forum

Take a minute to read the FAQ t the link in my sig

Try this bit of code and take a look at the attachment. It isn't the most elegant, but should be agood starting place for you





Option Explicit


Sub AddNumber6()
Dim r As Long, rBottom As Long

Application.ScreenUpdating = False

With ActiveSheet
rBottom = .Cells(.Rows.Count, 2).End(xlUp).Row

'best to go bottoms up
For r = rBottom To 2 Step -1
If .Rows(r).Cells(2).Value = 5 Then
.Rows(r + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Rows(r + 1).Cells(2).Value = 6
.Rows(r + 1).Cells(3).Value = "New City"
End If
Next r
End With

Application.ScreenUpdating = True


End Sub

[aka1999]

Thank you and it works perfectly in the sample spreadsheet. However, when I try to use it for my spreadsheet and change the details it doesn't work. In my spreadsheet I have to enter data whenever I encounter 14 in the column 32, then a new row after 14 needs to be inserted with 15 in Column 32 and NewCity in Column 36.
I changed few parameters but I am not doing it right and it's insert a row around after 200 rows and also not inserting it correctly.

[Paul_Hossler]

Original:



Is there a way to add a new row with same data everytime I encounter Row ID 5 in the 3rd column per the below screenshot?
I want to add a row with Row ID 6 and Name NewCity.


Revised:



In my spreadsheet I have to enter data whenever I encounter 14 in the column 32,
then a new row after 14 needs to be inserted with 15 in Column 32 and NewCity in Column 36.


31018




Option Explicit


Sub AddNumber15()
Dim r As Long, rBottom As Long

Application.ScreenUpdating = False

With ActiveSheet
rBottom = .Cells(.Rows.Count, 32).End(xlUp).Row

'best to go bottoms up
For r = rBottom To 2 Step -1
If .Rows(r).Cells(32).Value = 14 Then
.Rows(r + 1).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
.Rows(r + 1).Cells(32).Value = 15
.Rows(r + 1).Cells(36).Value = "New City"
End If
Next r
End With

Application.ScreenUpdating = True


End Sub

[aka1999]

Thanks a lot Paul, much appreciate it.