Excel Macro to insert a new row [Solved/Closed]

Joper - Jun 15, 2010 at 11:16 PM - Latest reply: rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen
- Jun 16, 2010 at 11:49 AM
Hello,

I would like to create a macro in excel to automatically insert a new blank row immediately after the last row of filled-in data and for this newly inserted blank row to be formatted (including conditional formats) as the filled-in row before it.

Can you please tell me the macro code that would achieve this?

Thank you very much in advance.

See more 

4 replies

Best answer
michelsmith 131 Posts Thursday May 27, 2010Registration date November 16, 2010 Last seen - Jun 16, 2010 at 02:00 AM
1
Thank you
Check the information it may be help you.......

http://www.mvps.org/dmcritchie/excel/insrtrow.htm

Thank you, michelsmith 1

Something to say? Add comment

CCM has helped 1902 users this month

rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 16, 2010 at 04:13 AM
0
Thank you
But there is always blank row at the bottom, so what to insert ???
Yes but, what I didn"t say earlier is that the entered data is several thousand lines of long rows of data segmented along several categories.

These, asset registers get updated by many different people in various countries with varying degrees of comfort with worksheets.

Invariably, they will insert a new row in a manner that ends-up messing up the format and formulas imbeded within it...then the'll try to fix it and end-up messing something else.

THAT'S WHAT I'M TRYING TO PREVENT. By adding a macro that inserts a perfectly formated new row at some logical point within the segmented worksheets.

This is easy enough to do by just recording a macro then going through the right steps on how to insert and format a row, then saving the macro.

The problem is, that such macro will always insert the row in the same "address" or "spot" that it was originally recorded...rather than immediately above ANY cell that the mouse has clicked on before keying in the macro.

This variable (checking a series of cells (rows) with data along a column, stopping the cursor at the blank cell at the bottom of the series of filled-in rows and inserting a new formatted row immediately above the blank row) is probably easy enough to code in VB...except that I'm not that familiar with VB which is why I'm seeking help here.

Thanks for asking and for any suggestions you may have.
rizvisa1 4481 Posts Thursday January 28, 2010Registration dateContributorStatus January 6, 2016 Last seen - Jun 16, 2010 at 11:49 AM
If the issue is the row location, then find out the new row

Lets say column A will always have a values

lLastRow = Cells(Rows.count, "A").end(xlup).row

This gives you the last used row (unless you sheet is blank in which case it is 1)