Inserting Blank rows after every row upto 2500 rows

M

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
 
D

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
 
M

Muhammed Rafeek M

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
 
M

Manju

Don said:
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
Click to expand...


Sorry Sir, Your first code didn't work.
 
M

Manju

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

Gord Dibben

Manju

Don's code works fine for me.

What didn't it do for you?


Gord Dibben MS Excel MVP
 
D

DonCam65

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.
 
M

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.
 
Top