Inserting a blank row after every alternate item of a list

[DJ]

Hi,


I need help with a problem. Please help.


I have a list in a column. Lets say Sheet1 has a list of names from A1:A50 and I need to insert a blank row after every item of the list. I tried to select every alternate row and insert new row but it inserts a row afer every 2nd item which is obviously not a solution.


Is there any solution to this problem?


Thanks,

DJ

 

[NARAYANK991]

Hi DJ ,


If you do not have a problem with formulae , then what you can do is use the following formula in an unused column , copy the formula down as far as required , copy the result and paste it back in column A.


=IF(MOD((ROW(H6)-ROW($H$6))/2,1)<>0,"",OFFSET($H$6:$H$9,(ROW(H6)-ROW($H$6))/2,0))


I have assumed that your data is in column H , from H6 through H9.


Change these address references to suit your data.


Narayan

 

[sdsurzh]

Hi,


Copy the below code and run it will work.


Sub InsertBlankRow()

For r = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1

Rows(r).Insert Shift:=xlDown

Next r

End Sub


Source:http://www.excelbanter.com/showthread.php?t=79271


Thanks,

Suresh Kumar S

 

[Debraj]

Deb: Hye DJ! You can do this by SORT!!

DJ: by Sort.. How?

Deb: OK, Insert a blank row.. i.e A:A.. Write 1, at A1, 2 at A2.. and then Fill Handle Series.. It will come up with 1,2,3... 50

Now Copy 1 to 50.. and paste the same at A51. (which are currently Blank Row)

Now Select all Cells.. and SORT.. by Column A..

It will Sort.1,1..2,2..3,3... and so on.. where 1st 1 is your data.. and 2nd 1 is blank from the bottom part of the sheet..

Dont forget to delete the inserted Row at A:A..


for more detail visit..

http://chandoo.org/forums/topic/vba-code-requiered-for-adding-blank-row#post-33669


Regards,

Deb.


PS:

I tried to select every alternate row and insert new row

You have to select each row.. individually by pressing CTRL to insert row at each row..

 

[DJ]

Hi All,


Thank you so much for the help.


DJ

 

[BBKP]

Hi DJ ,


If you do not have a problem with formulae , then what you can do is use the following formula in an unused column , copy the formula down as far as required , copy the result and paste it back in column A.


=IF(MOD((ROW(H6)-ROW($H$6))/2,1)&#60;&#62;0,"",OFFSET($H$6:$H$9,(ROW(H6)-ROW($H$6))/2,0))


I have assumed that your data is in column H , from H6 through H9.


Change these address references to suit your data.


Narayan

Hi Narayan

I was looking for a solution for automatically inserting rows in a list. Icopied your formula above but cant figure out how to treat #60, does it represent a row or column?

 

[Deepak]

Hi Narayan

I was looking for a solution for automatically inserting rows in a list. Icopied your formula above but cant figure out how to treat #60, does it represent a row or column?

If there's some unusual chars in a formula/code then it was happen due to forum migration, check this link for further info on this!

http://chandoo.org/forum/threads/upgraded-forums-old-vba-codes-formula-handling.12080/

Formula is

=IF(MOD((ROW(H6)-ROW($H$6))/2,1)<>0,"",OFFSET($H$6:$H$9,(ROW(H6)-ROW($H$6))/2,0))

 

[NARAYANK991]

Hi ,

@Deepak : Thanks.

I have corrected the formula in the original post.

Narayan

 

[BBKP]

Noted thank you very much