Need help with a vba script / loop

sammy1981

New Member
Joined
Jun 28, 2018
Messages
20
Hello,

I have a list of product codes
coulmn a has the product code
column b has quantity
column c & d contain a basic formula

I need to insert a string of numbers into every fourth row
so after 3 product codes, insert blank line and enter "12345678" into column a and enter "1" into column b

the catch is, that I need to leave the first 12 product codes alone, so i would need after 12 products to insert the first row with "12345678" into column a and enter "1" into column b, and then after every 3 product to insert the line again.

I came across the following code, however i cannot get it to start after the 12th product only

Dim Last As Integer

Dim emptyRow As Integer

Last = Range("A" & Rows.Count).End(xlUp).Row

For emptyRow = Last To 14 Step -3

If Not Cells(emptyRow, 1).Value = "" Then

Rows(emptyRow).Resize(1).Insert

Range(Cells(emptyRow, "A"), Cells(emptyRow, "C")).Value = Array("12345678", "1", "")

End If

Next emptyRow

End Sub


i guess i can change "last to" if we can get the loop to start after the 12th product

so in short if i have 1000 numbers in column a
i need it to go to line 13 and insert a row and enter "12345678"
then do the same in row17 then row 21 etc
thanks in advance
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
How about
Code:
Sub InsertRws()
   Dim i As Long
   Dim Rng As Range
   
   Set Rng = Range("A13:B13")
   For i = 16 To Range("A" & Rows.Count).End(xlUp).Row Step 3
      Set Rng = Union(Rng, Range("A" & i).Resize(, 2))
   Next i
   Rng.EntireRow.Insert
   Rng.Offset(-1).Value = Array(12345678, 1)
End Sub
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
55,526
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,127,536
Messages
5,625,390
Members
416,099
Latest member
sudarsan23

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