Macro for autro row insert ?

[Rob]

Attempting to insert blank rows from row three to row 100
other than manually inserting them. How would you createe
an auto row insert macro but only btween certain row #'s?
Also, would it be possible to autoextraxt the blanks at
some poitn?
thanks

 

[Frank Kabel]

Hi Rob
to delete blank rows have a look at
http://www.cpearson.com/excel/deleting.htm#DeleteBlankRows

For inserting 97 rows use something like the following macro (you may
have to adapt the starting point according to your needs)

sub insert_many_rows()
activesheet.cells(4,1).Resize(97,1).EntireRow.insert
end sub

 

[David McRitchie]

Hi Rob,
You might also want to take a look at my
Insert Row(s) using a Macro to maintain formulas
http://www.mvps.org/dmcritchie/excel/insrtrow.htm

When inserting and deleting rows you want to be careful
how you used formulas that refer to other rows. For these
you probably want to be using OFFSET.

You should also be familiar with the fill handle
http://www.mvps.org/dmcritchie/excel/fillhand.htm
for propagating cell content.

If column A has actual blank cells (not spaces) you can delete
the entire rows for such cells without a loop.
macro del_COLA_empty within
Delete Cells/Rows in Range, based on empty cells, or cells with specific values
http://www.mvps.org/dmcritchie/excel/delempty.htm