Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Need to add rows based on previous column values

  1. #1
    New Member
    Join Date
    Feb 2018
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Need to add rows based on previous column values

    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.

    JobNum Company Plant Asm Opr Operation
    106144 GCB GCB 0 10 MECHENG
    106144 GCB GCB 0 20 MACH
    106144 GCB GCB 0 30 FAB
    106144 GCB GCB 0 40 ASSEM
    106144 GCB GCB 0 50 TEST

    108422 GCB GCB 0 10 FAB
    108422 GCB GCB 0 20 MACH
    108446 GCB GCB 0 10 MACH
    108449 GCB GCB 0 10 FAB
    108449 GCB GCB 0 20 MACH

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,369
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Need to add rows based on previous column values

    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.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  3. #3
    New Member
    Join Date
    Feb 2018
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to add rows based on previous column values

    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?

    A B C D E F
    JobNum Company Plant Asm Opr Operation
    106144 GCB GCB 0 10 MECHENG
    106144 GCB GCB 0 20 MACH
    106144 GCB GCB 0 30 FAB
    106144 GCB GCB 0 40 ASSEM
    106144 GCB GCB 0 50 TEST
    106144 GCB GCB 0 60 PAINT-PC
    108037 GCB GCB 0 10 MACH
    108358 GCB GCB 0 10 MACH
    108396 GCB GCB 0 10 MACH
    108422 GCB GCB 0 10 FAB
    108422 GCB GCB 0 20 MACH
    108423 GCB GCB 0 10 FAB
    108423 GCB GCB 0 20 MACH
    108426 GCB GCB 0 10 MACH
    108426 GCB GCB 0 20 REWORK
    108427 GCB GCB 0 10 FAB
    108427 GCB GCB 0 20 MACH
    108431 GCB GCB 0 10 MACH
    108432 GCB GCB 0 10 FAB
    108432 GCB GCB 0 20 MACH
    108436 GCB GCB 0 10 FAB
    108437 GCB GCB 0 10 FAB
    108438 GCB GCB 0 10 FAB
    108439 GCB GCB 0 10 FAB
    108440 GCB GCB 0 10 FAB
    108446 GCB GCB 0 10 MACH
    108449 GCB GCB 0 10 FAB
    108449 GCB GCB 0 20 MACH
    108480 GCB GCB 0 10 ASSEM
    108480 GCB GCB 0 20 FAB
    108480 GCB GCB 0 30 MACH
    108485 GCB GCB 0 10 MACH

  4. #4
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,369
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Need to add rows based on previous column values

    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?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  5. #5
    New Member
    Join Date
    Feb 2018
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to add rows based on previous column values

    Quote Originally Posted by Fluff View Post
    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?
    I would like to be able to run it when I need to run it. Column F would always be PAINT-PC.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,369
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Need to add rows based on previous column values

    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
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  7. #7
    New Member
    Join Date
    Feb 2018
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to add rows based on previous column values

    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

  8. #8
    New Member
    Join Date
    Feb 2018
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to add rows based on previous column values

    It will always be zero

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    11,369
    Post Thanks / Like
    Mentioned
    216 Post(s)
    Tagged
    14 Thread(s)

    Default Re: Need to add rows based on previous column values

    I can't count!!
    it should be
    Code:
    Range("A" & Rw).Resize(2, 4).FillDown
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 2003 & 2013 on Win 7

  10. #10
    New Member
    Join Date
    Feb 2018
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Need to add rows based on previous column values

    Nevermind. I got it! Thank you VERY much!!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •