Changing InsertRows macro to select entire last row

[Alistar3000]

Hi,

I'm using the InsertRows macro found at http://dmcritchie.mvps.org/excel/insrtrow.htm to add rows in a worksheet and copy the formulas from the cells in the last row.

The macro as it's written on that page makes a selection based on the current active cell. I've tried adapting it to select the last row with data, so that it will add a new row below that, with no luck - the closest I have got is copying the row without any formulas.

Any help with adapting would be much appreciated.

Thanks

[snb]

Example:

if row 5 contains the formulae to be copied:

Please Login or Register  to view this content.

[Alistar3000]

That sort of works. I changed it to:

Rows(2).Copy Rows(Cells.SpecialCells(xlCellTypeLastCell).Rows +1)

To try and get it to add a row under the last row with data, which works until a row is added, then deleted, as the next row will still be under the deleted row leaving a blank row between (and it gets worse if even more rows are deleted and added).

Also, this copies the values and the formulae. Is there a way to get it to only copy the formulae? So that the cells don't display the data from row 2?

Thanks

[snb]

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.