Insert rows by macro?

Hi,

I've created a macro to assign to a button that inserts a row above say row 10, then another macro to assign to a different button that inserts a row above row 20. My problem is, having clicked the first button a row is inserted above row 10 but of course row 20 now becomes row 21, so now I need the second button to insert a row above row 21 instead of 20.
Is there any way I can write a macro that remembers the old row numbers?
Hope I haven't confused you all as much as I've confused myself
PS I'm not familiar with Visual Basic, I just use Excel's 'record a macro' function.

Thanks for listening.

Bore Da Celfyn.

Is there a feature in your spreadsheet that you're trying to insert the row above?

Hi,

Rather than inserting rows starting at the top, change the macro to insert the highest row number first. i.e. First add a row above row 20, then above row 10.

HTH

Originally Posted by Richard Buttrey

Hi,

Rather than inserting rows starting at the top, change the macro to insert the highest row number first. i.e. First add a row above row 20, then above row 10.

HTH

Sorry, forget that advice, I'd missed the point about this being two macros.

Why not just keep a cell variable in the workbook which is incremented every time you run macro 1. Then when you run macro 2 use that variable to work out which row should be inserted.

HTH

If you could post an example, it will help all with helping you! It will be possible to identify the point at which to insert the row.

Hi,

As Sweep has said it might be easier if you upload a workbook. However in essence you need to name a cell somewhere that will hold the variable, call it say 'RowNo'.

Then as the last line of Macro1 add:

Please Login or Register  to view this content.

Then in the second macro use some code like:

Please Login or Register  to view this content.

You may want to use another variable if row 20 (which is hard coded in the above) changes, and use that variable instead of 20.

HTH

How about looking for the next title, and inserting a row there?

Please Login or Register  to view this content.

Hi,

Why not just use a single macro. e.g.

Please Login or Register  to view this content.

This will insert a new row immediately above the next section title below the active cell when you run the macro.

Rgds

Please Login or Register  to view this content.