Inserting Blank rows after every row upto 2500 rows

[Manju]

Is there a command to insert blank rows automatically between already
existing data of more than 2000 lines?
I need this to automatically copy and paste with ceratin number of
blank rows to another software. Now what I do is a laborious process
and takes a lot of time
Can anyone please help? Thanks in advance

 

[Don Guillett]

this will insert rows
Sub insertrows()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For i = lr To 2 Step -1
Rows(i).Insert
Next i
End Sub

OR, if you just need space,consider doubling the row height.
Sub makerowbigger()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Rows("1:" & lr).RowHeight = 25
End Sub

 

[Don Guillett]

this will insert rows
Sub insertrows()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For i = lr To 2 Step -1
Rows(i).Insert
Next i
End Sub

OR, if you just need space,consider doubling the row height.
Sub makerowbigger()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Rows("1:" & lr).RowHeight = 25
End Sub

 

[Guest]

pls try this code

Sub Macro1()
Dim totR As Variant
Dim k As Integer
ActiveSheet.UsedRange.Select
totR = Selection.Rows.Count
k = 1
For i = 2000 To totR Step 2000
Range("A" & i + k).Select
Selection.EntireRow.Insert
k = k + 1
Next i
Range("A1").Select
End Sub

 

[Guest]

pls try this code

Sub Macro1()
Dim totR As Variant
Dim k As Integer
ActiveSheet.UsedRange.Select
totR = Selection.Rows.Count
k = 1
For i = 2000 To totR Step 2000
Range("A" & i + k).Select
Selection.EntireRow.Insert
k = k + 1
Next i
Range("A1").Select
End Sub

 

[Manju]

this will insert rows
Sub insertrows()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For i = lr To 2 Step -1
Rows(i).Insert
Next i
End Sub

OR, if you just need space,consider doubling the row height.
Sub makerowbigger()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Rows("1:" & lr).RowHeight = 25
End Sub

Sorry Sir, Your first code didn't work.

 

[Manju]

this will insert rows
Sub insertrows()
lr = Cells(Rows.Count, "a").End(xlUp).Row
For i = lr To 2 Step -1
Rows(i).Insert
Next i
End Sub

OR, if you just need space,consider doubling the row height.
Sub makerowbigger()
lr = Cells(Rows.Count, "a").End(xlUp).Row
Rows("1:" & lr).RowHeight = 25
End Sub

Sorry Sir, Your first code didn't work.

 

[Manju]

Dear Rafeek, Thanks
I had to insert manually row number for this to work. Any way thanks.
that will do.

 

[Manju]

Dear Rafeek, Thanks
I had to insert manually row number for this to work. Any way thanks.
that will do.

 

[Gord Dibben]

Manju

Don's code works fine for me.

What didn't it do for you?


Gord Dibben MS Excel MVP

 

[Gord Dibben]

Manju

Don's code works fine for me.

What didn't it do for you?


Gord Dibben MS Excel MVP

 

[Guest]

Manju
If you are not happy with vb code an alternative is to set up a new column
and insert number 1,3,5 etc until the last row of data.
Then continue with 2,4,6 etc until you match the number of odd rows.
Note you can use autofill - don't enter every number!.
Then select the whole block and sort on the column with the numbers.
You will then have 1=data,2=blank,3=data, 4=blank etc.
Whole operation takes less than a minute to do.

 

[Guest]

Manju
If you are not happy with vb code an alternative is to set up a new column
and insert number 1,3,5 etc until the last row of data.
Then continue with 2,4,6 etc until you match the number of odd rows.
Note you can use autofill - don't enter every number!.
Then select the whole block and sort on the column with the numbers.
You will then have 1=data,2=blank,3=data, 4=blank etc.
Whole operation takes less than a minute to do.

 

[Manju]

Dear Sir,
That's what I require!. I am still new to VB. This kind of ideas is
what I like and usually execute. Thanks a ton.

 

[Manju]

Dear Sir,
That's what I require!. I am still new to VB. This kind of ideas is
what I like and usually execute. Thanks a ton.

 

[Don Guillett]

Whatever makes you happy....

 

[Don Guillett]

Whatever makes you happy....