Insert rows based on the value on column F

**sramchan** · 10-31-2009, 06:47 AM

Hi All

I want to insert rows based on the coloumn F (has 6.00 on it)

2061 01.08.2009 01.08.2009 6.00

Since the coloumn F has value 6 on it, i want to insert 5 rows below the current row

Could you please help me doing it?

Million Thanks
sramchan

**renatolacerda** · 10-31-2009, 08:27 AM

Macros, Macros Macros.... :D

Dim MyLine As Integer
Sub AddRow_ColumnF(Line As Integer, Q As Integer)
Rows(Line + 1 & ":" & Line + Q).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 6 Then
Call AddRow_ColumnF(MyLine, Range("F" & MyLine).Value)
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
MyLine = ActiveCell.Row
End Sub

Ps: Put in the sheet you using.

This is it!!!!
Renato Lacerda

**sramchan** · 10-31-2009, 09:27 AM

Thanks for replying renatolacerda

Sorry for being dumb

Could you please explain how do i create the macro

I go to tools --> Macro --> "InsertRow"

It then opens up a visual basic editor

do i put your code inside it?

**sramchan** · 11-01-2009, 05:04 PM

can someone help?

**jaslake** · 11-02-2009, 12:15 AM

Hi sramchan
The code given you (not wrapped in code tags) is not complete. If you are willing to show me what you have and what you wish it to look like (before and after), perhaps I can help.
John

**sramchan** · 11-02-2009, 07:36 AM

this is how my file looks like

857 2050 Standby X 01.08.2009 01.08.2009 2.00
975 2050 Standby X 01.08.2009 01.08.2009 2.00
611 2050 Standby X 01.08.2009 01.08.2009 2.00
277 2050 Standby X 01.08.2009 01.08.2009 2.00
934 2050 Standby X 01.08.2009 01.08.2009 3.00

If i have number 2 on the last coloumn, i want one more row and the dates need to be incremented by 1.

857 2050 Standby X 01.08.2009 01.08.2009 1.00
857 2050 Standby X 02.08.2009 02.08.2009 1.00
975 2050 Standby X 01.08.2009 01.08.2009 1.00
975 2050 Standby X 02.08.2009 02.08.2009 1.00
611 2050 Standby X 01.08.2009 01.08.2009 1.00
611 2050 Standby X 02.08.2009 02.08.2009 1.00
277 2050 Standby X 01.08.2009 01.08.2009 1.00
277 2050 Standby X 02.08.2009 02.08.2009 1.00
934 2050 Standby X 01.08.2009 01.08.2009 1.00
934 2050 Standby X 02.08.2009 02.08.2009 1.00
934 2050 Standby X 03.08.2009 03.08.2009 1.00

thanks for the help

**sramchan** · 11-02-2009, 01:30 PM

is there any other information that is needed

**shg** · 11-02-2009, 01:44 PM

Yes; post a workbook.

**sramchan** · 11-03-2009, 06:04 AM

Please find the data i have right now. (sample_input)
and the output expected from it (sample_output)

**jaslake** · 11-03-2009, 02:01 PM

Hi sramchan
The attached workbook has procedures that appears to accomplish what you're trying to do except that I can't seem to get the leading 0's on the date fields. Sheet1 is your original input, Sheet3 is your original output. All the activity of the procedures take place on Sheet2.
See if it works for you and let me know of any issues. John