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
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
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
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,089
Members
448,548
Latest member
harryls

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
Back
Top