Need to add rows based on previous column values

C

csimpson24

New Member
Joined
Feb 22, 2018
Messages
22
Hello all,

Ripping my hair out over this one and cannot find an answer anywhere. Here is an example of my data. I want to automatically add a new row after the last job entry. So in the example below, I would add a new row for job 106144 with Opr 60, a new row for job 108422 with Opr 30, a new row for Job 108466 with Opr 20, and so on. Any help would be GREATLY appreciated. It's been years since I've written in VBA by the way.

[TABLE="width: 527"]
<tbody>[TR]
[TD][TABLE="width: 527"]
<tbody>[TR]
[TD]JobNum[/TD]
[TD]Company[/TD]
[TD]Plant[/TD]
[TD]Asm[/TD]
[TD]Opr[/TD]
[TD]Operation[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 527"]
<colgroup><col><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]106144[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]MECHENG[/TD]
[/TR]
[TR]
[TD]106144[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]106144[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]106144[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]40[/TD]
[TD]ASSEM[/TD]
[/TR]
[TR]
[TD]106144[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]TEST[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 527"]
<colgroup><col><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]108422[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108422[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]MACH[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 527"]
<colgroup><col><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]108446[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108449[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108449[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]MACH[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Sort by date Sort by votes
Hi & welcome to the board.

You haven't give us much to go on, but try this
Code: 
Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column <> 6 Then Exit Sub
Application.EnableEvents = False
   With Target
      .Offset(1).EntireRow.Insert
      .Offset(, -5).Resize(2, 5).FillDown
      .Offset(1, -1).Value = .Offset(, -1).Value + 10
   End With
Application.EnableEvents = True
End Sub
It needs to go in the sheet module.
 
Upvote 0
Thank you! I can provide more information. What do you need? Here's a portion of my spreadsheet. The red is an example of what I would like to have inserted automatically.
Once I place your code into sheet view, how does it run?

[TABLE="width: 527"]
<colgroup><col><col span="2"><col span="2"><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]JobNum[/TD]
[TD]Company[/TD]
[TD]Plant[/TD]
[TD]Asm[/TD]
[TD]Opr[/TD]
[TD]Operation[/TD]
[/TR]
[TR]
[TD]106144[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]MECHENG[/TD]
[/TR]
[TR]
[TD]106144[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]106144[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]106144[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]40[/TD]
[TD]ASSEM[/TD]
[/TR]
[TR]
[TD]106144[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]TEST[/TD]
[/TR]
[TR]
[TD]106144[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]60[/TD]
[TD]PAINT-PC[/TD]
[/TR]
[TR]
[TD]108037[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108358[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108396[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108422[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108422[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108423[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108423[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108426[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108426[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]REWORK[/TD]
[/TR]
[TR]
[TD]108427[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108427[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108431[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108432[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108432[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108436[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108437[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108438[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108439[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108440[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108446[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108449[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108449[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108480[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]ASSEM[/TD]
[/TR]
[TR]
[TD]108480[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]20[/TD]
[TD]FAB[/TD]
[/TR]
[TR]
[TD]108480[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]30[/TD]
[TD]MACH[/TD]
[/TR]
[TR]
[TD]108485[/TD]
[TD]GCB[/TD]
[TD]GCB[/TD]
[TD]0[/TD]
[TD]10[/TD]
[TD]MACH[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
When do you want this to run?
When you enter data in a particular column, or whenever you feel like it?
How will the macro know what value to put in col F?
 
Upvote 0
Fluff said:
When do you want this to run?
When you enter data in a particular column, or whenever you feel like it?
How will the macro know what value to put in col F?
Click to expand...

I would like to be able to run it when I need to run it. Column F would always be PAINT-PC.
 
Upvote 0
Ok, try
Code: 
Sub AddRow()

   Dim Rw As Long
   
   For Rw = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
      If Range("A" & Rw) <> Range("A" & Rw + 1).Value Then
         Rows(Rw + 1).Insert
         Range("A" & Rw).Resize(2, 3).FillDown
         Range("E" & Rw + 1).Value = Range("E" & Rw).Value + 10
         Range("F" & Rw + 1).Value = "Paint-PC"
      End If
   Next Rw
         
End Sub
 
Upvote 0
Works amazingly well! One small request. Column D is blank in the new row. Where do I add that in your code? I am blown away with how much I've lost since doing VBA 10 years ago... lol
 
Upvote 0
I can't count!!
it should be
Code: 
Range("A" & Rw).Resize(2, [COLOR=#ff0000]4[/COLOR]).FillDown
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
Call a macro based on text in column C (same row that is active).
Replies
8
Views
119
6StringJazzer
6StringJazzer
R
  • Solved
VBA: Merging rows after a particular row, based off of matching entries in column
2
Replies
19
Views
956
Georgiboy
Georgiboy
S
  • Question Question
Need code that adds multiple new rows to table to include a check box in column "L" for each new row
Replies
0
Views
539
sspatriots
S
L
  • Question Question
VBA Code to add in blank rows at a specific point, then do a formula in that blank row
Replies
1
Views
784
Peter_SSs
Peter_SSs
M
  • Question Question
Auto adjust row heights based on specific column
Replies
1
Views
405
XLfriend777
XLfriend777

Forum statistics

Threads
1,223,911
Messages
6,175,323
Members
452,635
Latest member
laura12345

Share this page

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