VBA code to insert a variable # of rows between each existing row matching certain criteria. Help please!

ktsmith21

New Member
Joined
Feb 25, 2015
Messages
16
Hello, I need some help on VBA, please. I've inlcuded an short example below of what I am trying to achieve.

I am trying to figure out the VBA to insert a variable number of rows (based on the value in Settings!C13) between existing rows on the worksheet 'UNIT_PRICE_COMP'. Some rows are category headers, and this is reflected by the value in column B being less than 1000.

Essentially, I need VBA for the following: For each cell in B < 1000, Insert X number of rows, where X = Settings!C13.

Finally, in column G, I need to add the name of the Partner, from the settings page. I need to include the value from Settings!C7 on the first row of any added rows, Settings!C9 on the next, and Settings!C11 on row 3 (if there is a third inserted row).

In the example below, the 'Settings' tab values are as follows:
Settings!C7 = PARTNER 1
Settings!C9 = PARTNER 2
Settings!C11 = PARTNER 3
Settings!C13 = 2

Any help is greatly appreciated!

Column BColumn CColumn DColumn EColumn FColumn G
Row 3ITEM NO.CHAPTERITEM DESCRIPTIONUNITTYPEPARTNER
Row 4
Row 5101.01 - ROADWROADWAY REMOVALSD
Row 610001.01 - ROADWPREPARING ROWACD
inserted by VBAPARTNER 1
inserted by VBAPARTNER 2
Row 710101.01 - ROADWREMOVING CONC (PAV)SYD
inserted by VBAPARTNER 1
inserted by VBAPARTNER 2
Row 810201.01 - ROADWREMOVING CONC (RIPRAP)SYD
inserted by VBAPARTNER 1
inserted by VBAPARTNER 2


 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,074
Office Version
2010
Platform
Windows
Your description is asking for extra rows when B < 1000, however your example appearrs to be asking for extra rows when B >= 1000. I have implemented the latter, it is easily changed if that is wrong.
try this code, I have used variant arrays which makes it very fast
VBA Code:
Sub test()
With Worksheets("settings")
 partners = Range(.Cells(7, 3), .Cells(11, 3))
 C13 = Range(.Cells(13, 3), .Cells(13, 3))
End With
Dim outarr() As Variant
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 7))
ReDim outarr(1 To lastrow * 4, 1 To 7)
'copy headers
  For j = 1 To 6
    outarr(1, j) = inarr(1, j)
  Next j
indi = 2
For i = 2 To lastrow
  ' copy a row
  For j = 1 To 6
    outarr(indi, j) = inarr(i, j)
  Next j
  indi = indi + 1
  ' check if extra rows needed
  If inarr(i, 2) >= 1000 Then
     For k = 0 To C13 - 1
      outarr(indi, 7) = partners(1 + k * 2, 1)
      indi = indi + 1
     Next k
  End If
Next i
Range(Cells(1, 1), Cells(indi, 7)) = outarr

End Sub
.
 

Forum statistics

Threads
1,089,579
Messages
5,409,114
Members
403,252
Latest member
Keithn1720

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top