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

sandy...@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

"K" <kamra...@yahoo.co.uk> wrote in message
news:0b5c196f-052a-4817...@e25g2000prg.googlegroups.com...

[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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
dguil...@austin.rr.com

"K" <kamra...@yahoo.co.uk> wrote in message
news:0b5c196f-052a-4817...@e25g2000prg.googlegroups.com...

[K]

On Feb 15, 1:51 pm, "Don Guillett" <dguille...@austin.rr.com> wrote:
> 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

> dguille...@austin.rr.com"K" <kamranr1...@yahoo.co.uk> wrote in message

> > Please if any body can help- Hide quoted text -
>
> - 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

sandy...@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

"K" <kamra...@yahoo.co.uk> wrote in message

news:857962ce-efc1-44f9...@s13g2000prd.googlegroups.com...

[K]

On Feb 15, 2:31 pm, "Sandy Mann" <sandyma...@mailinator.com> wrote:
> 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
>

> sandyma...@mailinator.com
> Replace @mailinator.com with @tiscali.co.uk
>

> 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

sandy...@mailinator.com
Replace @mailinator.com with @tiscali.co.uk

"K" <kamra...@yahoo.co.uk> wrote in message

news:08fdc94b-5ae2-4697...@d21g2000prf.googlegroups.com...