Macro for inserting a line

[Ryyanks2]

Hi,
I need help with making a macro in Excel. I Need it to insert a line below every 60 in the Duration column. I need this to happen for a chart size of A-K but be able to be done to mutliple charts without touching the ones it has already done! Thanks in advance for all the help!

Network	Period	Indicators	Label1	Time	Days	(000s)	(000s)	(000s)	(000s)	Duration
scooby	7	e4	4	6	2	1	6	8	tugj	30
scooby	8	e5	4	6	2	1	6	8	tugj	30
scooby	9	e6	4	6	2	1	6	8	tugj	30
scooby	10	e7	4	6	2	1	6	8	tugj	30
scooby	11	e8	4	6	2	1	6	8	tugj	30
scooby	12	e9	4	6	2	1	6	8	tugj	30
scooby	13	e10	4	6	2	1	6	8	tugj	60
scooby	14	e11	4	6	2	1	6	8	tugj	60
scooby	15	e12	4	6	2	1	6	8	tugj	60
scooby	16	e13	4	6	2	1	6	8	tugj	60
scooby	17	e14	4	6	2	1	6	8	tugj	60
scooby	18	e15	4	6	2	1	6	8	tugj	60
scooby	19	e16	4	6	2	1	6	8	tugj	60
scooby	20	e17	4	6	2	1	6	8	tugj	60
scooby	21	e18	4	6	2	1	6	8	tugj	60
scooby	22	e19	4	6	2	1	6	8	tugj	60
scooby	23	e20	4	6	2	1	6	8	tugj	60
scooby	24	e21	4	6	2	1	6	8	tugj	60
scooby	25	e22	4	6	2	1	6	8	tugj	60
scooby	26	e23	4	6	2	1	6	8	tugj	60
scooby	27	e24	4	6	2	1	6	8	tugj	60
scooby	28	e25	4	6	2	1	6	8	tugj	60
scooby	29	e26	4	6	2	1	6	8	tugj	60
scooby	30	e27	4	6	2	1	6	8	tugj	60
scooby	31	e28	4	6	2	1	6	8	tugj	60
scooby	32	e29	4	6	2	1	6	8	tugj	60
scooby	33	e30	4	6	2	1	6	8	tugj	60

<colgroup><col width="64" span="11" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[Rick Rothstein]

Give this macro a try...

Sub InsertRowBelowDuration60()
  Dim R As Long
  Application.ScreenUpdating = False
  For R = Cells(Rows.Count, "K").End(xlUp).Row To 2 Step -1
    If Cells(R, "K").Value = 60 Then Rows(R).Insert
  Next
  Application.ScreenUpdating = True
End Sub

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (InsertRowBelowDuration60) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[Norie]

Perhaps.

For I = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
    If Range("K" & I).Value = 60 Then
        Range("A" & I).Resize(, 11).Insert xlShiftDown
    End If
Next i

 

[Ryyanks2]

Hi thanks for such a quick response! Is it possible to make it relative because the duration wont always be in K

Thanks!

 

[Rick Rothstein]

Hi thanks for such a quick response! Is it possible to make it relative because the duration wont always be in K

Give this modification to the code I posted in Message #2 a try...

[table="width: 500"]
[tr]
	[td]Sub InsertRowBelowDuration60()
  Dim R As Long, DurationCol As String
  DurationCol = Split(Rows(1).Find("Duration", , xlValues, xlWhole, , , False, , False).Address(1, 0), "$")(0)
  Application.ScreenUpdating = False
  For R = Cells(Rows.Count, DurationCol).End(xlUp).Row To 2 Step -1
    If Cells(R, DurationCol).Value = 60 Then Rows(R).Insert
  Next
  Application.ScreenUpdating = True
End Sub[/td]
[/tr]
[/table]

 

[Norie]

This assumes the headers are in row 1.

    Res = Application.Match("Duration", Rows(1), 0)

    If Not IsError(Res) Then 

        For I = Range("A" & Rows.Count).End(xlUp).Row To 2 Step -1
            If Cells(I, Res).Value = 60 Then
                Range("A" & I).Resize(, 11).Insert xlShiftDown
            End If
        Next I

    End If