How to create macro to create new row and format it as the row above

[Luke82]

Hi,
I need to create macro that autmatically inserts new row and formats it the same as a row above. I wouldn't have problem if the new row was inserted at the bottom, however in my case i have rows with records and after the last record I have a blank row (which I want to stay the same). Below the blank row are 3 rows that display statistics for the records above. Perfectly, the macro would insert a new row between the last record and the blank row and copy all formatting from the row above. There is some data on the right side so it would have to copy formatings only for a range A:BI.

Thanks for any help in advance.

[HaHoBe]

Hi, Luke82,

maybe this code can give you a start:

Please Login or Register  to view this content.

If the data is aligned you may try it like this, making sure that in COlumn A thereŽs either a formula or a value and the range below is really empty:

Please Login or Register  to view this content.

Ciao,
Holger

[Luke82]

Thanks for quick reply. It works, however I need to actually select a cell in the last row but I would like the macro to find the last not empty cell in BJ column select it and insert the row below.

[DnDXan]

Hi, Luke82,

To Piggyback on what HaHoBe did:

This will aloow you to Insert a Row and copy Row above it into inserted Row. without selecting a cell. it finds the last Row used and goes from there.

Please Login or Register  to view this content.

DnDXan

[Luke82]

Thanks, but my problem is that I have rows with records in range A:BJ and below them I have an ampty row (it needs to stay empty) and below that row I have 3 rows in range A:BF that get data from the records above and summarise it. In that case I can't use last used row but want to use last used cell in column BJ to get the ladt row used by the last record. Not sure if I made it clear enough.

[Luke82]

Hi again,
I managed to do it but now I have different problem. Below my records I have 3 rows. The first one counts number of cells that have number 3, the second counts the one that have number 2 and the third one the ones with number 1. To do that I use formula, i.e.: "=COUNTIF(D3:D41,3)". The strange thing is that if I use macro to add new row the formula doesn't extend to 42. However, when I create a new row manually the formula extends.
The question is how to make formula to extend when using macro?

[HaHoBe]

Hi, Luke,

what about changing A5 to BJ3?

The question is how to make formula to extend when using macro?

IŽd have a go with putting in new formulas into that range as well. Or change the formula to read like

Please Login or Register  to view this content.

and adapt the row number and the item to count accordingly to refelct the position as well as the last cell used in Column D.

Ciao,
Holger

[Luke82]

Sorted. Thank you all for help