MACRO TO INSERT ROWS

[K]

Hi, I have numbers in coloumn "A" (Please see below)

A
1
2
3
4
5

I want macro which should add 6 rows between those numbers
which I mentioned above then put numbers back in sequense.
For example macro should add 6 rows from row 2 in which I have
number "2" in coloumn "A" cell 2 and once rows been added then
there will be no numbers in those added rows coloumn "A" cells so
macro should go in cell "A1" and put numbers back in sequense
LIKE :-
ADDED ROWS BY MACRO
1
2
row added
row added
row added
row added
row added
row added
3
4
5

PUT NUMBER BACK IN SEQUENSE OR DRAG NUMBERS
1
2
3
4
5
6
7
8
9
10
11
Please if any body can help

 

[Sandy Mann]

I assume that you want to select the starting cell by licking into it and
then running a Macro something like this:

Option Explicit
Sub InsertIt()
Dim LastRow As Long
Dim StartRow As Long

StartRow = ActiveCell.Row

Cells(StartRow + 1, 1).Resize(6, 1).EntireRow.Insert

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

Application.ScreenUpdating = False
With Range(Cells(StartRow, 1), Cells(LastRow, 1))
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, _
Step:=1, Trend:=False
End With
Application.ScreenUpdating = True


End Sub


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Don Guillett]

One way to find the number 2 and insert rows and renumber column
Sub addrows_renumber()
rti = 5
rta = Columns(1).Find(2).Row + 1
Rows(rta & ":" & rta + rti).Insert
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:A" & lr).DataSeries step:=1, Stop:=lr
End Sub

 

[K]

One way to find the number 2 and insert rows and renumber column
Sub addrows_renumber()
rti = 5
rta = Columns(1).Find(2).Row + 1
Rows(rta & ":" & rta + rti).Insert
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:A" & lr).DataSeries step:=1, Stop:=lr
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






- Show quoted text -

Thanks Sandy i did little changing in your macro and i got what i want
i just changed this line
StartRow = Cells(Rows.Count, 1).End(xlUp).Row
to
StartRow = Cells(Rows.Count, 1).End(xlUp).Row - 1
Thanks Don to you aswell as your Macro was also helpful

 

[Sandy Mann]

If that is what you want then it is fine but be warned that it will throw a
error 1004 at line:

With Range(Cells(StartRow, 1), Cells(LastRow, 1))

if you select Row 1

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

One way to find the number 2 and insert rows and renumber column
Sub addrows_renumber()
rti = 5
rta = Columns(1).Find(2).Row + 1
Rows(rta & ":" & rta + rti).Insert
lr = Cells(Rows.Count, "a").End(xlUp).Row
Range("A1:A" & lr).DataSeries step:=1, Stop:=lr
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software






- Show quoted text -

Thanks Sandy i did little changing in your macro and i got what i want
i just changed this line
StartRow = Cells(Rows.Count, 1).End(xlUp).Row
to
StartRow = Cells(Rows.Count, 1).End(xlUp).Row - 1
Thanks Don to you aswell as your Macro was also helpful

 

[K]

If that is what you want then it is fine but be warned that it will throw a
error 1004 at line:

 With Range(Cells(StartRow, 1), Cells(LastRow, 1))

if you select Row 1

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk






Thanks Sandy i did little changing in your macro and i got what i want
i just changed this line
StartRow = Cells(Rows.Count, 1).End(xlUp).Row
to
StartRow = Cells(Rows.Count, 1).End(xlUp).Row - 1
Thanks Don to you aswell as your Macro was also helpful- Hide quoted text -

- Show quoted text -

its not giving any error at the moment as I tried but thanks for
letting me know.
Please do you know that what should I add in macro that when macro
insert
rows then in those rows it should also merge the cells from coloumn
"I" to coloumn
"N"

 

[Sandy Mann]

I would recommend that you don't merge cells but rather "Center Across
Selection" because there can be problems later on when you, (manually), try
to select a column that has merged cells.

To see the syntax for the code turn on the Macro recorder and do what you
want manually then change the "With Selection" of the recorded code to "With
Range(whatever your range is) and then delete the line selecting the range.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

If that is what you want then it is fine but be warned that it will throw
a
error 1004 at line:

With Range(Cells(StartRow, 1), Cells(LastRow, 1))

if you select Row 1

--
Regards,

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk






Thanks Sandy i did little changing in your macro and i got what i want
i just changed this line
StartRow = Cells(Rows.Count, 1).End(xlUp).Row
to
StartRow = Cells(Rows.Count, 1).End(xlUp).Row - 1
Thanks Don to you aswell as your Macro was also helpful- Hide quoted
text -

- Show quoted text -

its not giving any error at the moment as I tried but thanks for
letting me know.
Please do you know that what should I add in macro that when macro
insert
rows then in those rows it should also merge the cells from coloumn
"I" to coloumn
"N"