need macro to insert a blank row between every 2nd row of data 1

[Jaffey]

I need a macro that will automatically insert a blank row between every 2nd row of data in a sheet. I would prefer not to have to record a macro to do it because there's hundreds of rows. Any help would be appreciated.

 

[PHV]

And what have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[DaleWatson123321]

Hi Jaffey,

This should help. I can appreciate that while the results one gets from recording a macro are helpful, there is MUCH more in-depth knowledge one needs to gain (in order to create an effective piece of code).

Sub Insert_Rows()
'Inserts row every 2nd row.
'Place cursor on top row of data.

Application.ScreenUpdating = False
toprow = ActiveCell.Row

'Rows are inserted based on last row of worksheet -
'i.e. rows are inserted for all data in sheet.
botmrow = ActiveCell.SpecialCells(xlLastCell).Row

'OPTION: Insert rows based on a specific column.
'The line commented-out below inserts entire rows, but
'inserted rows are based on last row used in column A.
'botmrow = [A65536].End(xlUp).Row

initrows = botmrow - toprow + 1
ttlrows = Int(initrows / 2) - 1
ActiveCell.Offset(2, 0).EntireRow.Insert shift:=xlDown
ii = 5

For i = 1 To ttlrows
    ActiveCell.Offset(ii, 0).EntireRow.Insert shift:=xlDown
    ii = ii + 3
Next i

Application.ScreenUpdating = True
End Sub

Regards, Dale Watson

 

[stevexff]

Dale

Whenever you're inserting rows, don't start at the top, start at the bottom. Then you aren't trying to hit a moving target, and your loop is much simpler...

Steve

[small]"Every program can be reduced by one instruction, and every program has at least one bug. Therefore, any program can be reduced to one instruction which doesn't work." (Object:erlDesignPatterns)[/small]