Results 1 to 3 of 3

Thread: Insert a new row based on a specific column value

  1. #1

    Insert a new row based on a specific column value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Gurus,

    I need some help on the below situation. I have columns from A to L with headers starting from Sl. No, Goal Name, Activity Name... etc.,
    Under 'Goal Name' header, I have Goal 1, Goal 2, etc., For each Goal, I have multiple activities.
    For ex: For Goal 1, I have 3 activities below. Likewise, for Goal 2, I have 2 activities and so on.

    Now I want to use macros, let's assume, I place a command button on this page and click on it, would ask me which 'Goal' I need to add a blank row at the end of the that particular Goal populated row.

    For ex: If I click on command button, it will ask the 'Goal Name'. I enter 'Goal 1' and then immediately, it should add a blank row below the row of 'Activity 1.2'. Likewise, If I enter 'Goal 2', a blank row should be added below 'Activity 2.1'.

    Kudos in advance for your help.


    Sl. No Goal Name Activity Name Activity WBS Team Priority Plan Start Date Actual Start Date Plan End Date Actual End Date Status % Complete
    [IMG]file:///C:/Users/xe206/AppData/Local/Temp/msohtmlclip1/01/clip_image002.png[/IMG]
    1 Goal 1 Activity 1 WBS 1 Team 1
    Activity 1.1 WBS 1.1 Team 1.1
    Activity 1.2 WBS 1.2 Team 1.2
    2 Goal 2 Activity 2 WBS 2 Team 2
    Activity 2.1 WBS 2.1 Team 2.1
    3 Goal 3 Acitivity 3 WBS 3 Team 3
    4 Goal 4 Activity 4 WBS 4 Team 4

  2. #2
    Good Morning All.. Appreciate if anyone provides a solution on this... Thanks in advance...

  3. #3
    I could able to initiate design and added some code. I inserted a command button on the sheet and clicking on it, invoked a user form:

    Private Sub cmdOpenForm_Click()

    frmData.Show

    End Sub


    In the user form, added Goal Name list box with values in it, added another command button. Clicking on the command button in the user form adding a blank row in the sheet. Code below:

    Private Sub frmAddRow_Click()

    If lstGoalName.Value = "Goal 1" Then

    Sheets("Sheet2").Range("A8").Select
    ActiveCell.EntireRow.Insert Shift:=xlDown


    Sheets("Sheet2").Range("A8:L8").Select
    Selection.Borders.Weight = xlThin


    ElseIf lstGoalName.Value = "Goal 2" Then


    Sheets("Sheet2").Range("A11").Select
    ActiveCell.EntireRow.Insert Shift:=xlDown


    Sheets("Sheet2").Range("A11:L11").Select
    Selection.Borders.Weight = xlThin

    End If

    End Sub


    Private Sub UserForm_Initialize()

    lstGoalName.RowSource = "Sheet2!AM1:AM4"

    End Sub


    However, in the code above, rows are fixed with A8 and A11 etc., for each goal 1, goal 2, etc., But adding few blank rows, it's jumbling up. So can you help me to enhance this code so that when I try to add a blank row it should check what is the last populated row for this particular goal and add a blank row underneath. For ex: If I select 'Goal 1' in the list box of user form and click on the add row button, it should check what is the last activity in the 'Activity Name' column for that particular Goal 1. It should find the last populated activity and then add a blank under it.

    Please help.

Posting Permissions

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