Adding Rows to Specific Sections within a Table

[emcgaffin]

Hello everyone!

I am trying to record a few macros that would allow me to add rows to each stage when necessary. Ideally I would click a button (I have multiple buttons) and it would insert a row in a specific stage. For example, if I need to add a row for Stage 0 I would like the macro to add a row directly above Stage 1. I have tried multiple times and I can not get it right.

Thanks for the help!

Project Plan Sample V2 Master.xlsm
	B	C	D	E
7	Task	Start	Projected End	Date
9	Stage 0
10	Task 1	5/24/2021	5/25/2021	5/31/2021
11	Task 2	6/1/2021	6/6/2021	5/31/2021
12	Task 3	6/5/2021	6/10/2021	5/31/2021
13	Task 4	7/1/2021	7/6/2021	5/31/2021
14	Stage 1: Define/Workflow
15	Task 1	5/30/2021	6/12/2021	5/31/2021
16	Task 2	6/12/2021	6/28/2021	5/31/2021
17	Task 3
18	Task 4
19	Task 5
20	Task 6
21	Task 7
22	Task 8
23	Stage 2: Integration
24	Task 1
25	Task 2
26	Task 3
27	Task 4
28	Stage 3: Testing
29	Task 1	15-Jun	28-Jun	31-May
30	Task 2	5-Jun	24-Jun	31-May
31	Task 3	30-Jun	15-Jul	31-May
32	Task 4	2-Jul	15-Jul	31-May
33	Task 5	5-Jun	20-Jun	31-May
Gantt Project Revised (2)

Cell Formulas
Range	Formula
C10	C10	=Start_Date
D11:D13	D11	=C11+5
E10:E33	E10	=IF(D10>0,TODAY(),"")

Named Ranges
Name	Refers To	Cells
'Gantt Project Revised (2)'!Start_Date	='Gantt Project Revised (2)'!$D$3	C10

 

[Zot]

This is an example on how you can detect Stage 1 and insert row. This is not probably what you exactly need since you did not mention what is the condition to insert row.

Sub Test()

Dim cell As Range, rngColA As Range

Set rngColA = Range("A3", Cells(Rows.Count, "A"))

For Each cell In rngColA
    Select Case cell
        Case "Stage 1: Define/Workflow"
            cell.EntireRow.Insert
        Case "Stage 2: Integration"
        
        Case "Stage 3: Testing"
        
    End Select
Next
    
End Sub

 

[Zot]

Oppss. my bad. The code is not correct. Here is the corrected one

Sub Test()

Dim cell As Range, rngColA As Range

Application.ScreenUpdating = False
Set rngColA = Range("A3", Cells(Rows.Count, "A").End(xlUp))

For Each cell In rngColA
    Select Case cell
        Case "Stage 1: Define/Workflow"
            cell.EntireRow.Insert
        Case "Stage 2: Integration"
        
        Case "Stage 3: Testing"
        
    End Select
Next
Application.ScreenUpdating = True
    
End Sub

 

[Zot]

Sorry. It cannot work that way. I will need to rework the code . I did not test it before

 

[Zot]

This is the corrected code

Sub Test()

Dim n As Long, eRow As Long
Dim cell As Range

Application.ScreenUpdating = False
eRow = Cells(Rows.Count, "B").End(xlUp).Row

For n = eRow To 7 Step -1
    Select Case Range("B" & n)
        Case "Stage 1: Define/Workflow"
            Range("B" & n).EntireRow.Insert
        Case "Stage 2: Integration"
        
        Case "Stage 3: Testing"
        
    End Select
Next
Application.ScreenUpdating = True
    
End Sub

 

[emcgaffin]

This is an example on how you can detect Stage 1 and insert row. This is not probably what you exactly need since you did not mention what is the condition to insert row.

Sub Test()

Dim cell As Range, rngColA As Range

Set rngColA = Range("A3", Cells(Rows.Count, "A"))

For Each cell In rngColA
    Select Case cell
        Case "Stage 1: Define/Workflow"
            cell.EntireRow.Insert
        Case "Stage 2: Integration"
       
        Case "Stage 3: Testing"
       
    End Select
Next
   
End Sub

Thanks Zot! I will mess with it now! Have a great day.

 

[emcgaffin]

This is the corrected code

Sub Test()

Dim n As Long, eRow As Long
Dim cell As Range

Application.ScreenUpdating = False
eRow = Cells(Rows.Count, "B").End(xlUp).Row

For n = eRow To 7 Step -1
    Select Case Range("B" & n)
        Case "Stage 1: Define/Workflow"
            Range("B" & n).EntireRow.Insert
        Case "Stage 2: Integration"
       
        Case "Stage 3: Testing"
       
    End Select
Next
Application.ScreenUpdating = True
   
End Sub

This was exactly what I needed. Thank you for the explanation! This works perfectly.

 

[Zot]

Thanks for updates. I 'm the one who messed up in the first place ?